Query Execution time miss match in HANA

[former_member462348]

Hi

Am facing some miss match in the Query execution time in HANA.

When i run the Data Preview for a Calc View for first 200 records, could see the results come in 40 seconds, where as when I generate the SQL for the same view and run the Plan viz, it take more than 1 minute.

And where as for certain views its completely the other way. In Plan Viz the Execution time shows less and the Data Preview execution time takes more than Plan Viz. Am not sure whats the reason for this time difference.

Have any one faced the same case earlier ??

Thanks
Rufus

[former_member607745]

Hi,

If I am not mistaken,then the SQL query you are running on SQL editor and if for the same SQL query you execute visualize plan,It should take almost equal execution time ,

For ex : select top 200 * from "_SYS_BIC".package_name/CA_TEST

I ran the above query in SQL editor on one of my view and in parallel execute visualize plan on it and it took almost same time for both.Also during data preview under Raw data,in show Log it took similar time to run.

[SergioG_TX]

the query execution time is done in HANA depending on the engine you use (Calc engine vs SQL engine).

The plan viz will probably take longer to execute as it needs to generate the entire plan verbose and display it to you on the plan viz window.. i do not think this is abnormal. Further, the more executions you perform on the same query/data model, it will cache and then will be served from memory rather than having to calculate it for the first time.. again this is normal behavior in SAP HANA

[lbreddemann]

This sounds very likely to be caused by comparing "apples to oranges".

The data preview generates a SQL statement including a TOP n clause, limiting not just how many records the front end actually reads, but telling the database how many records to be delivered as a maximum. This can have an impact on both the query plan but most definitively on the runtime of one of the most important query operators: materialisation.

With PlanViz (depending on the setting in HANA Studio) you don't the TOP n clause into your statements. Moreover, PlanViz will go on and fetch all results as part of the tracing (that's what PlanViz really is, an execution trace).

So, most commonly, PlanViz will cover more processing and thus the execution time will be longer than that in the data preview.

That's why it is important to realize that there is not really "the" performance of a view.
It's always the performance of the query against the view. Therefore, make sure to test the queries that will be used against the view - not just default SELECT * FROM or whatever the data preview gives you.