Looking for more information on Relational Optimization setting

[patrickbachmann]

Hi folks,

Since upgrading to SPS6 I noticed a new option in my analytic views called 'Allow relational optimization'.  I'm trying to learn more about this but all I'm finding in the latest SPS6 documentation is a very brief example;

EXAMPLE

You can enable relational optimization for your analytic view such as, Optimize stacked SQL for example, convert

SELECT a, SUM(X) FROM ( SELECT * FROM AV) GROUP BY A

to

SELECT A, SUM(X) FROM AV GROUP BY A

This example is not making sense to me at least the way it's written.  Can somebody perhaps explain differently or a better example of how this setting works?

Thanks,

-Patrick

[former_member184768 · Accepted solution]

Hi Patrick,

I personally have not tried this, but now keen to check this in next few days. But trying to add 2 cents based on the experience so far.

Recently during the discussion with SAP team, it was re-emphasized that with HANA, the thinking should move from "Relational" (Row based) to "Columnar".

Just to elaborate, SELECT * will generate the resultset with all the data from all the columns of the table / view, making ALL the columns involved in the column join, irrespective of the fact whether they are required for the final output or not. This can have huge impact on the performance, especially if you have a column with very high cardinality like Document number. You can compare the runtime for the following queries (or similar) and can observe the performance impact when the high cardinality column is involved.

1. SELECT REGION, sum(NET_SALES) from table group by REGION;
2. SELECT REGION, sum(NET_SALES) from (SELECT REGION, SALES_DOC_NO, NET_SALES from table) a group by REGION;

In the second query, the SALES_DOC_NO join will be included in the plan, even if it is not required for the final output. This is likely to have very negative impact on the query execution.

With the flag, such queries should be optimized. Especially in case of a third party tool (like MSTR) the generated queries tend to use inline queries and define a group by on top of such inline queries.

Having said this, I feel in case of a business requirement, where you need to use a Key Figure value in the WHERE condition, such setting will ensure that the inline query is retained. For example, if you need to eliminate data records where the Quantity is 0, you may not be able to get such rule implemented in the HANA Model (especially based on Calc View or view with default aggregation behavior). Such WHERE Quantity = 0 is likely to get converted to HAVING sum(Quantity) = 0, which is very unlikely situation (depending on the group by criteria). Hence, setting / unsetting the flag may have impact on how the data is expected to be reported and the performance impact.

Hope I tried to add some point of view to the discussion, but it would be nice to hear from other experts.

Thanks for bringing in an interesting topic, as always . I would like to try playing with such a flag and see the impact on the performance and result data.

Regards.

Ravi

[Former Member]

So this flag tells the SQL Optimizer to collapse subqueries. You need to be careful because this might have side-effects on the query output, especially with COUNT. This is because you might deliberately create subqueries to create COUNT on specific aggregation levels.

I've noticed in later patches of SP06, the SQL Optimizer will do some of this by default.

John

[Former Member]

Perhaps people could clarify a couple of things:

(1) There is no semantic difference between the two queries in the Guide example quoted above by Patrick.  Why does the guide warn: "In this case, if this flag is set counters and SELECT COUNT may deliver wrong results"?  Is this just a bad example?

(2) Is this warning just a general warning to people who don't understand much SQL, or does use of this flag mean that HANA will sometimes disregard semantic differences and rewrite the SQL regardless leading to a different (i.e. incorrect) result?

Regards,

Jeremy

[ruediger_karl]

Hi Jeremy,

the "relational optimization" flag enforces HANA to generate a hidden calculation view for processing on top of the analytical view. It allows to delegate relational optimizations, like nested/stacked SQL statements, to the CalcEngine, At the same time, you need to keep in mind that the HANA CalcEngine handles "select count ..." and "select count distinct ...) differently than classical SQL. Please see also customer note 1791464.

Regards, Ruediger

[Former Member]

Hi Ruedigar,

Thanks for your reply, and for the Note. I have limited HANA knowledge and was just starting to explore the Modelling Guide.  This is surprising stuff, but perhaps there is some background context that will somehow make this seem less hideous.

Is there any sort of description of the philosophy behind the Calculation Engine and the OLAP and Join Engines, something to rationalize why the Join and OLAP Engines support standard SQL while the Calculation Engine departs from it radically?  Since the results are different using different engine, is it always clear to users which engine will be used so that confusion can be minimised?

Regards, Jeremy

[lbreddemann]

Hi Jeremy,

actually the Developer Guide covers quite a bit of the calculation engine ideas and also refers to more detailed information in SAP notes

(e.g.1857202 - SQL Execution of calculation views).

The following SAP notes (especially the first one) explain the concept of calculation views and the effects on the "relational behavior" quite extensively.

1764658 HANA Calculation Engine Instantiation Process

1791464 Select count(*) on graphical Calculation Views

1958063 Unexpected results when mixing complex SQL functionalites with queries on Calcu

1805320 Cardinality Optimization of join by CalcEngine

1857202 SQL Execution of calculation views

And sure enough I agree with that there is a conceptual break compared to standard SQL views that should have been explained better.

- Lars

[Former Member]

Thanks Lars, those notes are very helpful.

Regards,

Jeremy