cancel
Showing results for 
Search instead for 
Did you mean: 

Hana Analytical Engine vs. SQL Engine

0 Kudos

Hi,

We are on a Hana 126 environment. We have a query like

SELECT COUNT(DISTINCT "/BIC/ZCOLUMN") 
FROM "ZTABLE";

wich is executed in about 300 MS, in a partitioned table with more than 1 B records.

When we change the query to

SELECT COUNT(DISTINCT "/BIC/ZCOLUMN") 
FROM "ZTABLE"
GROUP BY 1;

the query become slowly and takes about 300 S, so is 1000 times slower.

Through POPs used by both queries (in planviz), we can see the primary cause of the difference is that in first clause Hana uses Analytical engine, and in second clause the SQL engine.

We´ve tried with "USE_OLAP_PLAN" in second clause, but query is still using SQL engine (¿?¿?¿).

So, our question is:

- Where can we found information about the execution engine decision for a given SQL clause?

- Any advise or recomendation?

Thanks in advance!

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor

- Where can we found information about the execution engine decision for a given SQL clause?

A: you already found it: PlanViz is the right tool to review the optimizer result for any given query. There is, however, no fixed documentation of the built-in optimizer functions.

- Any advise or recomendation?

A: Given the scenario above, I'd recommend to use the faster option then.

0 Kudos

Hi Lars,

Thanks for your help Lars. In our case, the problem is that the query is automatically generated by an external reporting tool (Tableau), so it´s imposible to select the sql to be executed. Debugging some slow reports, we found the problem related above, and working with PlanViz we saw the problem. Our first option was use "USE_OLAP_PLANNER" but, as i said, Hana continues using SQL engine. So, now we have a lot of cuestions:

- Why when we add "GROUP BY 1" to SQL the planner uses SQL engine instead Analytic engine?

- Why using "WITH HINT "USE_OLAP_PLAN"" the planner is still using SQL engine?

And so on. We are searching for a solution to force the planner to use analytic engine, or a undestanding about the situations in wichs planners decides to use a given engine.

Thanks again!!

lbreddemann
Active Contributor

Ok, so what's happening here?

Are the two statements equivalent? No, they are not.

The first statement, without the GROUP BY clause, is using the aggregation function with the implicit grouping. This is something the OLAP engine can do.

The second statement is providing a constant (integer literal 1) to group by.
With other DBMS this form of GROUP BY clause allows referencing the COLUMNs to use for grouping by their occurrence in the SELECT statement. This is not SQL standard and HANA doesn't support it.

In order to still fulfil this request, HANA has to choose the best execution engine that supports only constants in the GROUP BY clause for the COUNT(DISTINCT) function. The OLAP engine doesn't do that, so any amount of HINT won't magically lead the execution to the OLAP engine. (It's called "HINT", not "FORCE" ...)

The question - still - is: why is the constant grouping used in the first place? So far I haven't seen this with any of the reporting clients (neither SAP's own, nor Cliq or Tableau).

former_member365886
Contributor

Hi,

Also to add to this execution plan time reference,is it correct to compare two different result set?

for 1st case you need to add both the task time = (300 MS to fetch data + Time taken to group by 1 ).

2nd case gives you data after group by order.

Regards,

Avik

0 Kudos

Hi,

Thanks both for your help. We´ll try to undestand the SQL generation in Tableau side.

Regards!