on 06-21-2017 4:11 PM
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!
- 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!!
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).
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
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.