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.
