Optimizing Query Performance

Objectives

After completing this lesson, you will be able to:

  • Describe query performance optimization

Performance Aspects

While query performance depends on overall design, data model maintenance, and query design, there are measures you can take to discover the reasons for long query runtimes and to improve performance.

Considerations for Query Performance Improvement

To improve query performance, you can do the following:

  • Check layout and filter settings in the query definition
  • Use query performance monitoring tools
  • Ensure that read mode is set appropriately
  • Ensure that the query execution mode is set appropriately
  • Check the data model
  • Improve performance on the database level

Layout and Filter Settings in the Query Definition

You can change the layout and filter settings directly in query definition.

Keep the number of characteristics in the rows or columns to a minimum and make use of free characteristics. Too many characteristics in the rows or columns means that the processor has to work hard to retrieve and format the extra data for all levels of the drilldown.

Filters and restrictions are created in the most efficient way available.

A common query design error is causing the OLAP processor to perform unnecessary steps to retrieve data. For example, if your query contains the characteristic Cal. Year/Month, filtered to the value 12.2014, and the query also includes the characteristic Calendar Year that is filtered to the fixed value 2014, you have a filter overlap that can affect performance.

The Exclude function within characteristic filtering is useful when you want to eliminate certain values from a range of values in the query. However, use this function sparingly, because too many exclusion instructions can start to slow down the query runtime. Consider using inclusions where possible, even if this means that the initial setup of the filters is more complex.

Variables also improve query performance by making data requests more specific. You can add a mandatory characteristic value variable so that reporting users must define a filter. This ensures that not all the detail values are read from the InfoProvider.

Further Aspects of Performance Optimization

Further settings and tools to optimize performance include the following:

Query Performance Monitoring Tools

Query performance monitoring helps to find the reason for slow query performance because you can view details about operations that are performed in the OLAP engine.

Read Mode: Appropriate Settings

A query read mode that does not meet the actual requirements can decrease performance. Unnecessary data may be loaded that is not used in the report.

Query Execution Mode: Appropriate Settings

The query execution mode determines the push down of query calculation operations from the ABAP runtime to the SAP HANA database to improve performance significantly.

Data Model Check

This is a task for an SAP BW/4HANA architect or administrator. There are various reasons why the data model can lead to poor reporting performance, such as the size of InfoProviders and their partitioning.

Performance Improvement at the Database Level

SAP BW/4HANA administrators and architects can also improve reporting performance on the database level, for example, by optimizing the database settings.

Query Monitor

For SAP BW/4HANA administrators, there are several performance monitoring tools which can be used to check the execution of queries in the SAP BW/4HANA system. These include the Trace Tool and the OLAP Cache Monitor.

In this course, we focus on the Query Monitor as a tool for analyzing the query runtime and the operations that are performed while the query is loaded.

The query monitor tests, checks, and manages SAP BW/4HANA queries and is intended for use by SAP BW/HANA administrators. You can open the query monitor via the transactions RSRT or RSRT1.

You use the query monitor to do the following:

  • Test SAP BW/4HANA queries using Execute + Debug
  • Specify SAP BW/HANA query properties such as Read Mode and Operations and SAP HANA
  • Regenerate SAP BW/HANA queries
  • Display technical information about SAP BW/HANA queries

In the query monitor, you enter the technical name of the query. To perform detailed analysis, you choose Execute + Debug. You can execute the query using the settings Display Statistics Data and Do not use cache to get an overview of the different steps performed and the runtime of each step.

Note
You can find a detailed example of the Execute + Debug function in the next lesson.

Query Read Mode

Query Read Mode determines how the OLAP processor reads data when executing a query and also during query navigation.

These modes can be managed on InfoProvider level (Runtime Profile Properties) as default values for the queries or individually for each query in the BW Query Monitor(transaction RSRT) or in BW Query Designer (Runtime Properties).

The following types of query read mode are supported:

Query Read Mode H = Query to be Read when you Navigate or Expand Hierarchies

  • OLAP processor only requests the data required for each query navigation status of the query.
  • Data across the hierarchy is aggregated by the database and transferred to the OLAP processor on the hierarchy level that is the lowest in the start list.

    When expanding a hierarchy node, the system intentionally must read this node's children from the database.

  • Transfers the smallest amount of data from the database to the OLAP processor.
  • Has the highest number of read processes.
  • Significantly improves performance for the initial execution in almost all cases, compared to the other two read modes, because only data that the user initially wants to see is requested.

Query Read Mode X = Query to Read Data During Navigation

  • OLAP processor only requests the data required for each query navigation status of the query.
  • Hierarchies are always imported completely at leaf level here.

  • Only has an effect on performance for queries with presentation hierarchies.

Query Read Mode A = Query to Read all Data at Once

  • Has only one read process.
  • Reads all data in the cache of the OLAP processor that is needed for all possible navigational steps of the executed query.
  • Aggregates and calculates all new navigational states from the data from the cache during navigation.

Log in to track your progress & complete quizzes