strange out of memory query

[Former Member]

Hello,

We are having weird SQL performance issue in the following SQL:

SELECT 

DIM."_BA1_IGL_ACCOUNT", "_BA1_C40FTRAN", "CURRENCY_TYPE",

SUM("KF_VALUE") AS "KF_VALUE_SUM"

FROM

"_SYS_BIC"."BNHP-UTILS.NewDrillDown/CV_DAILY_BALANCES"

( 'PLACEHOLDER' = ('$$IP_MODULE$$', '0'), 'PLACEHOLDER' = ('$$IP_BAL_DATE1$$', '20141231')) FACT  

  INNER JOIN "_SYS_BIC"."MD.Groupings/AT_GRP_GL_ACC_NUM"  DIM

ON (

DIM."_BA1_IGL_ACCOUNT" = FACT."_BA1_IGL_ACCOUNT"  AND

DIM."GL_ACCOUNT_GRP_CODE2" = FACT."GL_ACCOUNT_GRP_CODE2"  AND

DIM."GL_ACCOUNT_GRP_CODE1" = FACT."GL_ACCOUNT_GRP_CODE1"  AND

DIM."GL_ACCOUNT_GRP_CODE3" = FACT."GL_ACCOUNT_GRP_CODE3"  AND

DIM."GL_ACCOUNT_GRP_CODE4" = FACT."GL_ACCOUNT_GRP_CODE4"  AND

DIM."GL_ACCOUNT_GRP_CODE5" = FACT."GL_ACCOUNT_GRP_CODE5" )

WHERE ("CURRENCY_TYPE" = 'G') and

DIM."_BA1_IGL_ACCOUNT" = '0000099020'

--DIM."GL_ACCOUNT_GRP_CODE1" = '05'

GROUP BY

DIM."_BA1_IGL_ACCOUNT", "CURRENCY_TYPE","_BA1_C40FTRAN"

ORDER BY  DIM."_BA1_IGL_ACCOUNT" ASC, "CURRENCY_TYPE" ASC

If we unmark the where sentence to: DIM."GL_ACCOUNT_GRP_CODE1" = '05'   and change it with DIM."_BA1_IGL_ACCOUNT" = '0000099020'

although the query should return even more records it ends in 2 seconds!!

When checking the explain plan we discover:

Where DIM."_BA1_IGL_ACCOUNT" = '0000099020'

Where  DIM."GL_ACCOUNT_GRP_CODE1" = '05' 

When checking the visio plan:

Where DIM."_BA1_IGL_ACCOUNT" = '0000099020':

Where  DIM."GL_ACCOUNT_GRP_CODE1" = '05'  :

As you can see from the visio plan estimation in the working query the ATT view cost estimation is bigger than the CV cost estimation while in the out of memory query the estimation is exactly the opposite.

Is there a way to control which query will run first?

Please help!!!!

Thanks,

Amir

[lbreddemann]

Hi Amir,

no, you cannot control the order of execution here.

In fact I would assume that both of the query parts are executed in parallel anyhow.

Your PlanViz is unfortunately just the prepared statement but not the executed version - so it contains just the same information as explain plan does.

Can you click execute in the planviz editor and check the actual execution information?

Also: your model looks like a star schema/OLAP model, but you created it as a series of joins to an attribute view. That's kind of halfway between two options.

Why don't you create an analytic view or calculation view with star join instead?

That would provide SAP HANA with more options to optimize the statement execution.

- Lars

[Former Member]

Hi Lars,

1.The visio plan of the first query wont run - the query gets OOM...

2. We need to use this in BO IDT, There we are using several calculation views (which are built from a lot of other objects...) as the fact and Attribute views for common dimensions. our model is too complex to built on HANA (we have more than 2000 measures and 800 dimensions...)

The sql from the message is very similar to the one created by BO IDT (this SQL is just more simple to understand..) 

Thanks,

Amir

[lbreddemann]

Well with this kind of approach you leave SAP HANA not much choice.

It needs to materialize the result of the calculation view before joining it further.

If you would use a calculation view with star join, there would be more optimization options that eventually could avoid the OOM.

- Lars