Skip to Content
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 report this post you need to login first.

22 Comments

You must be Logged on to comment or reply to a post.

  1. Bertrand LAFOUGERE
    Really clever tips, I’ll check with the administrator if it’s running or not.

    To measure the performance differences I suppose that you use the BW Statistics cube ?

    (0) 
    1. Prakash Darji Post author
      Yes. You can use the BW Statistics cube to check the times. Uusally, to measure the advantages, I’ll just run a single run in a controlled manner. I’ll typically go to RSRT, and choose debug. Then I’ll display the stats data. I’ll record these times. Next, I’ll hit the “Generate Report” button. Then I’ll run the query again and choose “Display Stats”. Now I have a run of the query before and after generation. This will help me measure the benefits of optimization. Make sure you run this test on something that hasn’t been optimized recently. You can find that in table (RSRREPDIR).
      (0) 
      1. John Skrabak
        It seems to me that to perform a valid comparison you really need to run the query twice, regenerate the query, and then run the regenerated query. Then compare the results from the second execution to the third.

        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.

        (0) 
    1. Sume Chau
      Hi,
      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

      (0) 
  2. Pankaj Gupta
    Prakash

    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

    (0) 
    1. Prakash Darji Post author
      Hey 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.

      (0) 
      1. Sume Chau
        Hi,
        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

        (0) 
      2. Sume Chau
        Hi,
        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

        (0) 
    2. Pankaj Gupta
      Just found one note in help that system does optimize the code 1st time and then after 31 days. I never knew this. Does that mean optimization can be at most 31 days old if we have not set the mode to 9.
      (0) 
      1. Prakash Darji Post author
        Correct, except of course, if you run generation. When you generate the query you’re optimizing it as well. The reason I prefer this method is optimization may take quite some time for complicated reports. Thay is why I’m using the OPT_OCCURS flag to only optimize things that have been run since the last optimization. My logic is that if nobody is using it, why use system resources to optimize it. If yo uchange the option to 9 and say optimize every 7 days, then you’re utilizing system resources to optimize reports that may or may not need it or have been run recently. The benchmarking with the generation is I think a good balance. Analyze your change velocity of your system and quantify that to how many days should go by before performance degrades. Then schedule generation on reports that have been run since last generation.
        (0) 
    1. Prakash Darji Post author
      This process works for multicubes as well. When you are running the program and it prompts for infocube, this includes all dataproviders that queries are built off of (cubes, ODS, infoobject, etc…)
      (0) 
  3. Ben Belz
    interesting weblog, but i am having trouble finding any sap docs on the OPT_OCCURS or even the RSRT “optimization mode” options.  the oss notes listed consist mainly of corrections…

    thanks

    (0) 
  4. Alex Vladimirov
    Thank you for post, Darji.
    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.
    (0) 
  5. Sume Chau
    Hi,
    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

    (0) 
  6. HyunDoo Sung
    Hi Darji,

    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.

    (0) 
  7. Dave Elson
    I have noticed that the field OPT_OCCUR can have three values, blank, X and F.  Have you any idea what the F value means in this field?

    Thanks

    (0) 
  8. param Sivam
    Hi,

    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

    (0) 

Leave a Reply