Question Re: HANA SQL join behaviour in Technology Q&A

HANA SQL join behaviour

Former Member — Wed, 12 Aug 2015 06:26:25 GMT

Hello,

Hello, We have a simple SQL statements:

SELECT

Table__24."ZCONTRACT_NO",

Table__111."ACCOUNTING_PRODUCT_GRP_CODE1",

SUM(Table__24."KF_VALUE")

FROM

"_SYS_BIC"."MD.Groupings/AT_GRP_ACCOUNTING_PRODUCT" Table__111

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

('PLACEHOLDER' = ('$$IP_BAL_DATE1$$','20150331' ),'PLACEHOLDER' = ('$$IP_MODULE$$', '0')) Table__24

ON (Table__24."_BIC_CACCPRODG" = Table__111."_BIC_CACCPRODG" AND Table__24."ACCOUNTING_PRODUCT_GRP_CODE1" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE1" AND Table__24."ACCOUNTING_PRODUCT_GRP_CODE2" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE2" AND Table__24."ACCOUNTING_PRODUCT_GRP_CODE3" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE3" AND Table__24."ACCOUNTING_PRODUCT_GRP_CODE4" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE4" AND Table__24."ACCOUNTING_PRODUCT_GRP_CODE5" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE5")

WHERE

( Table__24."CURRENCY_TYPE" IN ('G') )

AND ( Table__111."ACCOUNTING_PRODUCT_GRP_CODE1" IN ( '11' )

AND Table__24."ZRDL_TS_DATE" <= '20150401'

)

GROUP BY

Table__24."ZCONTRACT_NO",

Table__111."ACCOUNTING_PRODUCT_GRP_CODE1"

We run this SQL twice: first time with the restriction:

AND Table__24."ZRDL_TS_DATE"

And second time without it.

The first viso plan:

The second viso plan:

As you can see with the restriction of "ZRDL_TS_DATE" the query returns 1,077,662 records and without it the query returns 101,875 records (in both queries we select the same fields…).

Why does it behaves this way?

Isn't it supposed to penetrate our restriction to the lowest level of the calculation view and only return the selected fields?

Thanks,

Amir

Re: HANA SQL join behaviour

chandan_praharaj — Wed, 12 Aug 2015 06:38:27 GMT

Hi Amir,

No this Where clause restriction is NOT passed to lowest level and restrict the data set. So this is showing as in the explain. You are only passing the IP in one view so it wont restrict for the whole view. Hope you got it.

Cheers!

Chandan

Re: HANA SQL join behaviour

Former Member — Wed, 12 Aug 2015 10:53:17 GMT

Hi Chandan,


chandan praharaj wrote:

No this Where clause restriction is NOT passed to lowest level and restrict the data set.

There's no such rule that where will not be passed at lowest level ever, also using input parameters you have a chance to explicit push down but there's no such rule as it depends on your expession and modeling inside the calculation view.

It's not clear if the push down is or not possible, anyhow the restriction you want to be pushed down is weak. Perhaps you are forcing him to bring all data to you.

Table__24."ZRDL_TS_DATE" <= '20150401'

Sugestion, try to use an exact date on filter and check the result on PlanViz again.

I guess the push down is happening but without it HANA Optimizer can be much better than with your filter.

In rare cases the push down is not desired because break the optimizations of Join node. In these cases the best shoot is try in a most recent revision because the optimizer are always being improved and maybe it behave better in a recent revision. BTW: What's yours?

Regards, Fernando Da Rós

Re: HANA SQL join behaviour

lucas_oliveira — Wed, 12 Aug 2015 12:30:03 GMT

Hi All,

In addition to what said. Isn't ZRDL_TS_DATE a calculated column? That might explain that behavior.

Using calculated columns in the where clause can force all the values from underlying tables to be retrieved before it can actually perform the calculation and then filter.

BRs,

Lucas de Oliveira

Re: HANA SQL join behaviour

Former Member — Wed, 19 Aug 2015 04:59:35 GMT

Hi everyone,

Lucas Oliveira ZRDL_TS_DATE is not a calculated column and no manipulations is done for this field.

Fernando Ros when we filter ZRDL_TS_DATE = '20150401' the push down is happening (no data is fatched..) . Maybe <= is not supported for push down?

I didn’t understand why a condition like ZRDL_TS_DATE" <= '20150401' might force to bring more data than without it.

We are in SP97

Thanks,

Amir

Re: HANA SQL join behaviour

Former Member — Wed, 19 Aug 2015 15:34:14 GMT

Hi Amir,

About bring more data than needed I explain with an issue I faced on SP7 on break of join optimization.

In a calc view joinning BKPF and BSEG, which the main filter is BUDAT (date) on BKPF (header) table... and BSEG is acessed after from BKPF date filtering with good performance.

After add an filter on subsequente projection BSEG-KOART=' ', the behavior changed to run selections in parallel for BSEG with filter KOART=' ' and BKPF with same filter BUDAT to join later. As many rows was acessed from BSEG the performance went down and memory usage increased.

So, what should helping to reduce number of rows (KOART) was in fact breaking the join optimization....

Solution applied at that time was adjuste the expression to (KOART = ' ' or KOART <> '1').. The OR broke the push down and preserved the join optimization.

Just to Remember: It happened ON that revision, ON that scenario... HANA DEV are enhancing such optimizers with a lot of inteliggence, so this sample is not needed anymore.

Anyhow, your case remind me this one and I guess could be happening similar issue. In this case if I have data since 2010, I'll read basically 5 years:

ZRDL_TS_DATE" <= '20150401'

Give a try. Perform a test with an expression that broke the push down optimization:

(ZRDL_TS_DATE" <= '20150401' OR ZRDL_TS_DATE = ZRDL_TS_DATE)

Regards, Fernando Da Rós