Skip to Content

Data manager ID generated for every query -> SQL plan cache is ignored

Hi!

I've noticed in my system that for every query (no matter via RSRT or via infoprovider data view in RSA1) in generated SQL-statement system appends a string with DMUID like:

SELECT something

FROM somewhere

WHERE where_clause

/*$ dmuid=0005PZGODYOBXA2JK5IWZ4IWL */

This leads to total ignoring of HANA SQL plan cache because every query gets own unique DMUID. And therefore reduces performance. I think it's some BW statistical stuff. Maybe someone knows how to disable that?

Thanks, Andrey.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 30, 2016 at 06:36 AM

    That's a good observation Andrey and not too uncommon for database literate folks to remark when they get a bit deeper into SAP BW.

    The main point here is that the comment with the DMUID is generated newly for every different SAP BW query. That means the DMUID will change when the rest of the query changes, which in turn leads to the same number of new SQL queries than without the comment.

    So, disabling this wouldn't enhance the usage of the SQL plan cache.

    BTW: the plan cache is never ignored, disabled or not used. The SQL queries are put into the cache and it can be used to analyse issues, e.g. performance problems on DB level and link the SQL queries back to SAP BW terms.

    Add comment
    10|10000 characters needed characters exceeded

    • Correct, these are independently prepared and optimized.

      So what? The query is not even using bind variables in the first place and fairly simple , too. You shouldn't loose too much time due to re-parsed statements here.

      For generally re-usable statements the ABAP layer provides the means to handle this very efficiently.

      However, for SAP BW the typical application is not to have many complex statements that are exactly the same run over and over again.

      Could you optimize the parse time for your example? Sure.

      Would the improvement be large enough to rectify taking out code that highly improves the options to analyse more complex scenarios efficiently? Probably not.

      Would your improvement be worth while a separate line of code in the data manager? Probably not again.