Calculation View performs well but performance degrades at time of filter

[Former Member]

Hello,

We have Calculation view in HANA which is union of two analytical views. The calculation view performs under 30 secs for 1 year dates and in 8 secs for one month date range.

However when user tries to filter the data retrieved in step 1 the filter set takes more than 1 min and 15 secs even for the full results that was retrieved in step 1. This is consistent in all applications (Webi, explorer and data preview in HANA studio) for a specific dimension.

We have checked the following

1) The indivdual ANALYTICS views that are part of the calculation view has good performance and is less than the calculation, so it should not be join issues as the analytics views perform well

2) The SQL statement generated by the calculation view becomes extremely complex with nested select when the filter for the dimension is performed.

3) We have not created any index on the dimension. This is our last resort we want to avoid creatng index on the dimension.

Any performance tips will be greatly appreciated.

[former_member182114]

Hi Han K,

Use "SQL Engine" for calculation view not always result in reduction of time, there are some cases that you have penaulties. What are the times running into CE for filtering and w/o?

What is your HANA DB version?

The internal behavior for the Execute In: "SQL Engine" changed dramatically on SP7, there's no more SQL being generated to be parsed against SQL parser.

The bad behavior happens on any kind of column filtering? Are these column fields from the original AN's or it's calculated?

After the mentioned UNION, what you have in general? Can you share visually also together with PlanViz Krishna ask you?

Regards, Fernando Da Rós

[Former Member]

Krishna and Ross,

To answer your questions

1) The columns fields are coming from the original AN's and are simply combined in nion. Pasted is the visual plan.

2) HANA DB Version : SP6

I understand SQL Engine does not always results in reduction of time, but I do no expect significant increase in time from 8.25 secs to 1 min and 30 sec as seen in our case. That in my mind is a 500% performance degration and it should not happen.

Regards,

-Hari

[former_member182114]

Hi Hari,

Behind this flag there's no magic but work... An always better work provided by SAP DEV team.

About the planviz, better you upload a .plz file as we can navigate into it otherwise this thread will become monstruous rsss

Anyhow, this is not necessary for two facts.

Fist: Yes, put SQL engine to work works perfect if you only have on your paths colunar tables or other graph calculation views.

It's not exact say that always will not work for Analytical because it can work, but the optimization delivered with this artifact is use SQL engine that doesn't materializes the result. So if you stay on one engine. PERFECT

Depending on what you have on the graphical calculation you may execute these two analytics and materialize the result not only TWO times but many times.

In one of many tries we did here on our calculations the time increased from 25 seconds to 10 minutes. In this case we kept it on CE.

Second: You need to try it on SP7, as I said earlier things above the hood changed. There's no SQL generated anymore... Now it goest directly from CE to SQL optimized plan (no more generate SQL and parse it later).

The performance increased in two manners by this:

- there's no waste of time generating the SQL and parsing the temporary SQL statment

- some optimizations were lost on this to SQL -> to qo

Suggested action plan:

- move to SP7

- try again and post the complete planviz or open a ticket

Best regards, Fernando Da Rós

[former_member182114]

Hi Hari,

I remind the SAP Note 1857202 - SQL Execution of calculation views.

Read the pdf attached "CalcViewSQLGenerationPublished.pdf"

There are more things there but you will like (or not) this:

"Do not include any other than Table or (Graphical) Calculation View as a data source. If Calculation View is included all of these mentioned rules must also hold for the included calculation view. Explicitly do not add any Analytic View, Attribute View, or Scripted Calculation View. This might break any optimization."

Regards, Fernando Da Rós

[Former Member]

Thank you Ros. Moving ahead I will upload a .plz file for visualize plan. Few  more questions for you. 1) I am not familiar with analyzing  the SQL PLAN statement. Looking at the SQL Plan, can you advise if  "Execute in" Property is turned on for  "SQL Engine". If it is turned on , then should it be turned on? Please advise. 2) Also if I need to set the flag to execute using the "SQL Engine" where do I set the flag. Is there a document for this Many Thanks, -Hari

[former_member182114]

Hi Hari,

About 1/2: It's an option you can see on semantics of your view. After revision 72 it's not visible directly on right box but in the propertis of the view.

For this case what you need to do is set to blank to do not use the SQL Engine.

Regards, Fernando Da Rós

[former_member182302]

Can you share the Visual Plan of the query you are trying? Interested to see at which level filter is happening and which step is actually taking more time for you.

Regards,

Krishna Tangudu