Analytic View - Allow Relational Optimization

[Former Member]

Hi,

Could somebody explain me in detail about this option ''Allow Relational Optimization'' in Analytic View.

Thanks

SR.

[former_member210482]

Hi SR,

Just to brief you about relational query optimization, Its basically restructuring your query to optimize query run time. So by checking this option, HANA tends to rewrite the query in optimized form. One such example is quoted in the developer guide. ie, removing subsets. Note that this option is provided only for analytical view. And it is recommended to use this feature when your view contains complex calculations.

To give a better understanding of optimization, check this example.

Example 1:

SELECT "SIDNUM" FROM "SCHEMA"."EMP" E1 WHERE "SAL" = (SELECT max("SAL") FROM

"SCHEMA"."EMP1" E2 WHERE E1.DEP=E2.DEP);

This example looks good, but the subquery may be executed for each employee or at least for each DEP which is costly. So by removing this subquery and rewriting it as shown below, your query will be more optimized.

SELECT "DEP", max("SAL")  as "m" INTO "SCHEMA"."temp" FROM "SCHEMA"."EMP1" GROUP BY "DEP";

SELECT "SIDNUM" FROM  "SCHEMA"."EMP" E, "SCHEMA"."temp" WHERE "SAL"="M" AND E.DEP=temp.DEP;

Example 2:

SELECT "num" FROM  WHERE "DEP" IN (SELECT "DEP" FROM "SCHEMA"."DEPT");

Here Optimized query:

SELECT "num"

FROM "SCHEMA"."EMP" E, "SCHEMA"."DEPT" D

WHERE E.DEP=D.DEP;

Hope this answers your query.

Cheers,

Safiyu

[Former Member]

Hi,

In analytic view we can create Calculated columns and counters to perform complex logics. In that case sub select statements created in database, which will effect the performance of analytic view.

If you check this option ''Allow Relational Optimization'' it will remove the sub select's in database and create a single select statement and it will improve the performance.

Regards,

Hariteja.

[vivekbhoj]

Hi,

It would be better for you to go through HANA Modeling Guide first as it will solve your queries like these:

http://help.sap.com/hana/SAP_HANA_Modeling_Guide_en.pdf

Regards,

Vivek

[Former Member]

Hi Vivek,

I have read this in Modeling guide, but still not able to understand. I am not good at SQL. May be this is the reason I am not able to understand..

Would be great if you can explain me in more detail...

thanks for the reply..

Siva.

[vivekbhoj]

Hi Siva,

I myself have not tried this:

From seeing the example in the Guide, it seems it will tell SQL Optimizer to collapse subqueries

You can also check below thread for more info regarding this setting:

Regards,

Vivek