Query Optimization
What is Query Optimization? It is based on internal storage and aggregation data that is collected by the OLAP processor. There are things that are different in each system like aggregates, size of tables, and variable values. That’s why the OLAP processor might generate different run schedules. How optimization works: This optimization has nothing to do with CBO or database statistics. Every time a query is started, BW checks if optimization would be beneficial. If yes, it sets the OPT_OCCURS flag in table RSRREPDIR. Once the query is generated the flag is reset. Using SE16 on table RSRREPDIR you can quickly findout which queries should be regenerated (OPT_OCCURS = X). The timestamp tells you when the last check was performed. Generally, as reports are run, they are almost always flagged to be optimized again. Executing and scheduling optimization: To optimize a query individually, go to RSRT and hit Generate Report. To schedule query optimization in mass on a periodic basis, you can schedule this by cube using program RSR_GEN_DIRECT_ALL_QUERIES. In your process chain, you can parallelize the optimization by running each cube in parallel. To enhance scheduling such that you can choose the OPT_OCCURS=X (only optimize queries that have been executed since last optimization), you can enhance this program by creating a Z Program. In most cases, this will speed up the optimization. Why use Query Optimization? When working with this mass generation, a few observations have been made. In general, I have seen anywhere between a 5% and a 250% increase in performance. Every time a query is run, the flag OPT_OCCURS marks the query “not-optimized”. This does not actually mean the performance has degenerated. In actuality, tests show that queries generally run ok for 2 to 5 days before re-generation is needed. This is due to how quickly your model changes. This means that if your data grows a lot, aggregates change, stats change, etc… The optimization changes the code of the query to optimize it on how the system currently looks. If this changes frequently, optimization may need to occur more often. If it changes in-frequently, then scheduling this every few weeks may not be a bad idea. The following enhancement allows you to only regenerate queries that have been run since being last optimized. To do this, you would copy program RSR_GEN_DIRECT_ALL_QUERIES to a Z Program, and then add the 2 lines of code below. If you wanted, you could also enhance this program to add the query technical name as an input field. That way, you could choose individual queries that you wanted to generate. * input-fields SELECT-OPTIONS: i_icube FOR rsrrepdir-infocube, i_OBjST FOR rsrrepdir-OBJSTAT, i_READMD FOR rsrrepdir-READMODE, i_AUTHOR FOR rsrrepdir-AUTHOR, i_CACHMD FOR rsrrepdir-CACHEMODE, *——————————————————- *START – Add this line i_OPTOCC FOR rsrrepdir-OPT_OCCURS, *END *——————————————————- i_PERSMD FOR rsrrepdir-PERSISTMODE. START-OF-SELECTION. SELECT * FROM rsrrepdir INTO TABLE l_t_rsrrepdir WHERE objvers = ‘A’ AND infocube IN i_icube AND objstat IN i_OBjST AND READMODE IN i_READMD AND AUTHOR IN i_AUTHOR AND CACHEMODE IN i_CACHMD AND PERSISTMODE IN i_PERSMD *——————————————————- *START – Add this line AND OPT_OCCURS IN i_OPTOCC *END *——————————————————- ORDER BY INFOCUBE compid. Benefits of query Optimization: One problem that you may notice when having queries with 2 very large complex structures is that it takes a while for the variable screen to come up. Running query optimization generally solves this problem. Also, query optimization is a must after applying any notes or support packs that effect OLAP. I leave it to you to test it out and see the performance improvements you get! Related OSS Notes: 755330, 771498, 659901, and 814911
To measure the performance differences I suppose that you use the BW Statistics cube ?
The first execution may have to perform physical DB reads to get the requested data. The second execution would almost certainly find all the necessary data in the database's buffer, and run faster. Then compare the stats of this second query to the stats third, regenerated query.
Regards -
Ron
I am little confused here.
There are two terms being used. Query Generation and Query Optimizaion. I think query generation means running the query.
OR
Is it generated when you choose the Generat report button in RSRT will generate the query?
Now, When I go to RSRT -> Technical Info -> Optimization Information, I see red dot and last update column is all zeros. Now how to fix this red thing. My RSRT setting is set to "0 - Query will be optimized after generation". My both OPT_OCCURS and OPT_OCCURS_TIME are blank fot this query in RSREPDIR table.
What should I do?
Please advice.
I will appreciate your help.
Sume
Excellent Weblog. I have related question.
Can you explain the meaning on "Optimization Mode" on RSRT Quert Property.
It can have value 0 1 or 9. Does it impact the regeneration process.
Pankaj
This does impact the regeneration process:
0 - Query will be optimized after generation
1 - Query optimization with individual period in days (you must specify number of days)
9 - Query Optimization inactive
Typically, you want to use the query optmization after generation. That way, when you generate the query, you are running the optimization activities. This still would require you to schedule generation or generate a query manually as this is just for optimization. You can go to the optimization directly through RSRT -> Technical Info -> Optimization Information. Because I always optimize after generation, I view this as part of the generation process.
I am little confused here.
There are two terms being used. Query Generation and Query Optimizaion. I think query generation means running the query.
OR
Is it generated when you choose the Generat report button in RSRT will generate the query?
Now, When I go to RSRT -> Technical Info -> Optimization Information, I see red dot and last update column is all zeros. Now how to fix this red thing. My RSRT setting is set to "0 - Query will be optimized after generation". My both OPT_OCCURS and OPT_OCCURS_TIME are blank fot this query in RSREPDIR table.
What should I do?
Please advice.
I will appreciate your help.
Sume
I am little confused here.
There are two terms being used. Query Generation and Query Optimizaion. I think query generation means running the query.
OR
Is it generated when you choose the Generat report button in RSRT will generate the query?
Now, When I go to RSRT -> Technical Info -> Optimization Information, I see red dot and last update column is all zeros. Now how to fix this red thing. My RSRT setting is set to "0 - Query will be optimized after generation". My both OPT_OCCURS and OPT_OCCURS_TIME are blank fot this query in RSREPDIR table.
What should I do?
Please advice.
I will appreciate your help.
Sume
Pankaj
Thanks,
Miguel P.
thanks
I have big problem (long time query after data re-loading), was read many forums and SAP notes,
but unsuccessfuly. Only your solution return the execution time for my queries to previous (normal) level. Thank you again.
I am little confused here.
There are two terms being used. Query Generation and Query Optimizaion. I think query generation means running the query.
OR
Is it generated when you choose the Generat report button in RSRT will generate the query?
Now, When I go to RSRT -> Technical Info -> Optimization Information, I see red dot and last update column is all zeros. Now how to fix this red thing. My RSRT setting is set to "0 - Query will be optimized after generation". My both OPT_OCCURS and OPT_OCCURS_TIME are blank fot this query in RSREPDIR table.
What should I do?
Please advice.
I will appreciate your help.
Sume
I have a question regarding Query optimization.
We are using BI70 with BIA Indexes.
Is it still applicable solution with BIA?
Best Regards,
HD Sung.
Thanks
I have a scenario, where more than 3 queries are consolidated in to one Web report
Example
1query for Table
1 query for Graph.
In this case how do I optimize ? Should i select each query individually?
Reason behind, like this we have 15+ report