Why are the execution times different in SQL trace and Planviz.

[former_member701788]

We have built a CDS view and exposed it as an Odata service. We ran the Odata URL and the runtime in the gateway is about 12 sec. To analyze further we captured the SQL trace in ST05. Below is the execution time we see. The OPEN operation in SQL trace took 9.45 sec.

I took the plan (using the option in the black box in the above figure) with the "with session variables" option. The plan below shows that execution time is only 658.01 ms. I also double-checked this with the Plan Cache and Max Execution time is 673.531 ms.

Also, this is from my S4HANA sandbox where the load on the system is very low. How can there be such a huge difference? I did refer to the below thread and could not find a clue. Can somebody throw some light on this?

Explanation by Lars

[lbreddemann]

The long-running parts of the query seem to be the prepare and open (run query) operations.

Both of these steps require that the tables and columns referenced in the query are loaded into main memory in SAP HANA.

Running the query - at least for the first time since the columns have been loaded to memory - may require additional once-off data loads and pre-computation (e.g. index rebuilds, join translation table builds).

Those effects would not be present to PlanViz traces executes after the initial long-running query was observed.

As a next step in the analysis of the long runtime, I would recommend unloading the tables in question and re-run the PlanVIz trace. Also, monitor the column load/unload trace (if active) to confirm how much column loading/unloading was involved in this query.

add: I have written about analysing a similar performance issue before: https://www.lbreddemann.org/one-in-a-million/

[former_member701788]

Hi Lars,

Thanks for the inputs. I first ran the query as is and it took 16sec in total is ST05, in Planviz it's 871ms. I unloaded all the tables in use, in the CDS and ran the query. The run time is now 2.9sec in ST05 trace and in Planviz, it is still the same 871ms. Looked in M_CS_ALL_COLUMNS and they are loaded.

Basically what I am trying to do is performance tune the CDS view. Without the actual time in PlanViz, I cannot do this because I cannot exactly pinpoint where the problem is. Also when I run the query the second time it is quite fast because of factors like buffering / cache etc. When I make a change in the CDS view and run it I want to run it like it's running for the first time. The only way I could figure out is to delete the Plan Cache ID. With this, I can see the same runtime as the first time. Surprisingly when I do this I still see the tables loaded in memory but the runtime is long. So I thought its basically taking time in the preparation phase but it is not so.

Any more pointers that can help me?

Regards,

Bharat.

[lbreddemann]

It reads like your experiment followed this timeline:

1) unload tables -> 2) run CDS query & trace ST05 -> 3) run the query with planviz again

That leaves out the unloading of the tables before the planviz trace.

Of course, if you actually change the CDS view, then any query using this view will require re-parsing/re-optimising and that can take time, too. Depending on the type of changes it might well be that there is substantial work required to prepare queries (e.g. rebuild join translation tables, invalidate & rebuild result caches, etc.).

All in all, based on my experience, I'd say this problem is likely beyond what can be reasonably solved within a Q&A forum question. I've provided several pointers, but beyond that a consulting engagement would be warranted.