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 bjvers = '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