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

[andrey_ryzhkov]

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.

[lbreddemann · Accepted solution]

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.

[andrey_ryzhkov · Accepted solution]

Hi, Lars!

Thanks for your answer!

It seems that DMUID is generated for every query, even if the meaningful part of the query didn't change. For example, i run two identical queries consequently (in data view of infoprovider (w/o DSO) in RSA1), DMUID is newly generated for both, as I see in SQL plan cache monitoring:

By "ignoring" cache I mean that it's never reused actually for BW-queries with DMUID (in my case). So every query must be prepared first (spending time for this) and then executed. Instead of immediately executed if the cache was reused.

[lbreddemann · Accepted solution]

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.