$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

Hana Analytical Engine vs. SQL Engine

Jun 21, 2017 at 03:11 PM

163

avatar image
Former Member

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Jun 23, 2017 at 08:11 AM
0

- 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.

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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!!

0

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).

1

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

1
Former Member

Hi,

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

Regards!

0