Query Performance

[Former Member]

Hi ,

Apart from creating of Aggregates;can any one of you list in detail the points to improve query performance.

Tahnx in Advance,

Vasu

[edwin_harpino · Accepted solution]

hi Vasu,

take a look

Prakash's weblog

/people/prakash.darji/blog/2006/01/27/query-creation-checklist

/people/prakash.darji/blog/2006/01/26/query-optimization

BW Performance Tuning Knowledge Center - SAP Developer Network (SDN)

[original link is broken]

performance docs on query

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3f66ba90-0201-0010-ac8d-b61...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/ce7fb368-0601-0010-64ba-fad...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c8c4d794-0501-0010-a693-918...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/064fed90-0201-0010-13ae-b16...

oss note

557870 'FAQ BW Query Performance'

and 567746 'Composite note BW 3.x performance Query and Web'.

[Former Member · Accepted solution]

Thanx Vijay and AHP,

But I wanted to know what are all the things to be taken

care in the query definition,Like using variables, filters,....etc...

[edwin_harpino · Accepted solution]

hi Vassu,

yes, check in Prakash's weblog

Query Creation Checklist

Prakash Darji

Many places I've gone have looked for "Best practices" when writing queries. While this list is not complete, I've been building on it as time goes on. Customers usually like having a list of "do's" and "don'ts" when it comes to query building. This list will help ensure that queries are built well (for performance and maintainability).

Walkthrough Checklist for Query Performance

1. If exclusions exist, make sure they exist in the global filter area. Try to remove exclusions by subtracting out inclusions.

2. Use Constant Selection to ignore filters in order to move more filters to the global filter area. (Use ABAPer to test and validate that this ensures better code)

3. Within structures, make sure the filter order exists with the highest level filter first.

4. Check code for all exit variables used in a report.

5. Move Time restrictions to a global filter whenever possible.

6. Within structures, use user exit variables to calculate things like QTD, YTD. This should generate better code than using overlapping restrictions to achieve the same thing. (Use ABAPer to test and validate that this ensures better code).

7. When queries are written on multiproviders, restrict to InfoProvider in global filter whenever possible. MultiProvider (MultiCube) queries require additional database table joins to read data compared to those queries against standard InfoCubes (InfoProviders), and you should therefore hardcode the infoprovider in the global filter whenever possible to eliminate this problem.

8. Move all global calculated and restricted key figures to local as to analyze any filters that can be removed and moved to the global definition in a query. Then you can change the calculated key figure and go back to utilizing the global calculated key figure if desired

9. If Alternative UOM solution is used, turn off query cache.

10. Set read mode of query based on static or dynamic. Reading data during navigation minimizes the impact on the R/3 database and application server resources because only data that the user requires will be retrieved. For queries involving large hierarchies with many nodes, it would be wise to select Read data during navigation and when expanding the hierarchy option to avoid reading data for the hierarchy nodes that are not expanded. Reserve the Read all data mode for special queries—for instance, when a majority of the users need a given query to slice and dice against all dimensions, or when the data is needed for data mining. This mode places heavy demand on database and memory resources and might impact other SAP BW processes and tasks.

11. Turn off formatting and results rows to minimize Frontend time whenever possible.

12. Check for nested hierarchies. Always a bad idea.

13. If "Display as hierarchy" is being used, look for other options to remove it to increase performance.

14. Use Constant Selection instead of SUMCT and SUMGT within formulas.

15. Do review of order of restrictions in formulas. Do as many restrictions as you can before calculations. Try to avoid calculations before restrictions.

16. Check Sequential vs Parallel read on Multiproviders.

17. Turn off warning messages on queries.

18. Check to see if performance improves by removing text display (Use ABAPer to test and validate that this ensures better code).

19. Check to see where currency conversions are happening if they are used.

20. Check aggregation and exception aggregation on calculated key figures. Before aggregation is generally slower and should not be used unless explicitly needed.

21. Avoid Cell Editor use if at all possible.

22. Make sure queries are regenerated in production using RSRT after changes to statistics, consistency changes, or aggregates.

23. Within the free characteristics, filter on the least granular objects first and make sure those come first in the order.

24. Leverage characteristics or navigational attributes rather than hierarchies. Using a hierarchy requires reading temporary hierarchy tables and creates additional overhead compared to characteristics and navigational attributes. Therefore, characteristics or navigational attributes result in significantly better query performance than hierarchies, especially as the size of the hierarchy (e.g., the number of nodes and levels) and the complexity of the selection criteria increase.

25. If hierarchies are used, minimize the number of nodes to include in the query results. Including all nodes in the query results (even the ones that are not needed or blank) slows down the query processing. The “not assigned” nodes in the hierarchy should be filtered out, and you should use a variable to reduce the number of hierarchy nodes selected.

[Former Member · Accepted solution]

Thanx AHP

[Former Member]

Hi Vasu,

Check OSS 557870.

Text message of OSS

This note contains a list of frequently asked questions relating to query peformance.

Overview of Questions

1. What kind of tools are available to monitor the overall

Query Performance?

2. Do I have to do something to enable such tools?

3. What kind of tools are available to analyse a specific query in

detail?

4. What kind of query performance problem do I have?

5. What can I do if the database proportion is high for all queries?

6. What can I do if the OLAP proportion is high for all queries?

7. What can I do if the client proportion is high for all queries?

8. Where can I get specific runtime information for one query?

9. What kind of query performance problems can I recognize using ST03N

values for a specific query?

10. What can I do if a query has a high database runtime?

11. What can I do if a query has a high OLAP runtime?

12. What can I do if a query has a high frontend runtime?

Question:

1. What kind of tools are available to monitor the overall

Query Performance?

Answers:

o BW Statistics

o BW Workload Analysis in ST03N (Use Export Mode!)

o Content of Table RSDDSTAT

Question:

2. Do I have to do something to enable such tools?

Answer:

o Yes, you need to turn on the BW Statistics:

RSA1, choose Tools -> BW statistics for InfoCubes

(Choose OLAP and WHM for your relevant Cubes)

Question:

3. What kind of tools are available to analyse a specific query in detail?

Answers:

o Transaction RSRT

o Transaction RSRTRACE

Question:

4. Do I have a overall query performance problem?

Answers:

o Use ST03N -> BW System load values to recognize the problem. Use the

number given in table 'Reporting - InfoCubes:Share of total time (s)'

to check if one of the columns %OLAP, %DB, %Frontend shows a high

number in all InfoCubes.

o You need to run ST03N in expert mode to get these values

Question:

5. What can I do if the database proportion is high for all queries?

Answers:

Check:

o If the database statistic strategy is set up properly for your

DB platform (above all for the BW specific tables)

o If database parameter set up accords with SAP Notes and SAP Services

(EarlyWatch)

o If Buffers, I/O, CPU, memory on the database server are exhausted?

o If Cube compression is used regularly

o If Database partitioning is used (not available on all DB platforms)

Question:

6. What can I do if the OLAP proportion is high for all queries?

Answers:

Check:

o If the CPUs on the application server are exhausted

o If the SAP R/3 memory set up is done properly (use TX ST02 to find

bottlenecks)

o If the read mode of the queries is unfavourable (RSRREPDIR, RSDDSTAT,

Customizing default)

Question:

7. What can I do if the client proportion is high for all queries?

Answer:

o Check whether most of your clients are connected via a WAN

connection and the amount of data which is transferred

is rather high.

Question:

8. Where can I get specific runtime information for one query?

Answers:

o Again you can use ST03N -> BW System Load

o Depending on the time frame you select, you get historical data or

current data.

o To get to a specific query you need to drill down using the InfoCube

name

o Use Aggregation Query to get more runtime information about a

single query. Use tab All data to get to the details.

(DB, OLAP, and Frontend time, plus Select/ Transferred records,

plus number of cells and formats)

Question:

9. What kind of query performance problems can I recognize using ST03N

values for a specific query?

Answers:

(Use Details to get the runtime segments)

o High Database Runtime

o High OLAP Runtime

o High Frontend Runtime

Question:

10. What can I do if a query has a high database runtime?

Answers:

o Check if an aggregate is suitable (use All data to get values

"selected records to transferred records", a high number here would

be an indicator for query performance improvement using an aggregate)

o Check if database statistics are update to data for the

Cube/Aggregate, use TX RSRV output (use database check for statistics

and indexes)

o Check if the read mode of the query is unfavourable - Recommended (H)

Question:

11. What can I do if a query has a high OLAP runtime?

Answers:

o Check if a high number of Cells transferred to the OLAP (use

"All data" to get value "No. of Cells")

o Use RSRT technical Information to check if any extra OLAP-processing

is necessary (Stock Query, Exception Aggregation, Calc. before

Aggregation, Virtual Char. Key Figures, Attributes in Calculated

Key Figs, Time-dependent Currency Translation)

together with a high number of records transferred.

o Check if a user exit Usage is involved in the OLAP runtime?

o Check if large hierarchies are used and the entry hierarchy level is

as deep as possible. This limits the levels of the

hierarchy that must be processed. Use SE16 on the inclusion

tables and use the List of Value feature on the column successor

and predecessor to see which entry level of the hierarchy is used.

- Check if a proper index on the inclusion table exist

Question:

12. What can I do if a query has a high frontend runtime?

Answers:

o Check if a very high number of cells and formattings are transferred

to the Frontend ( use "All data" to get value "No. of Cells") which

cause high network and frontend (processing) runtime.

o Check if frontend PC are within the recommendation (RAM, CPU Mhz)

o Check if the bandwidth for WAN connection is sufficient

Vijay

Message was edited by: Vijay Ganga