JOIN_THRU_AGGR - Not working from VIEW Properties but working in SELECT

[former_member667589]

Hello ,

Recent training on HANA Query optimizations has helped many of us to understand how HINTs work. I was able to identify a performance bottleneck, its logical reason through planviz and HINT that can improve the performance. I have observed below.

1. If I perform SELECT on graphical calculation view with all selections and HINT - JOIN_THRU_AGGR in SQL console, memory consumption and runtime is reduced by 20% (78 GB brought down to 53 GB)

SELECT <FIELDNAME> FROM <CALC VIEW> WHERE MANDT = '100' AND STORE = 'ABCD' WITH HINT 'JOIN_THRU_AGGR'

2. If I put the same HINT - JOIN_THRU_AGGR in Calculation view "View Properties" tab at semantic layer, memory consumption of view shoots up to 78 GB again.

SELECT <FIELDNAME> FROM <CALC VIEW> WHERE MANDT = '100' AND STORE = 'ABCD'

We are on HANA 2.0 SP04.

Did anybody faced similar issue ?

-Mandar.

[former_member673606]

Hi Mandar,

I have the exact same issue, did you get a solution for this ?

--Deba

[former_member667589]

Hello Deba,

No, not yet. Seems HINT is unexplored territory within HANA community.

We modified our design for time being as putting HINT in View Property was not helping us.

-Mandar.

[Bojan-lv-85]

Hi Mandar,

it would be helpful to let us know which key / value pair exactly you have used to define the hint. Please describe in more detail (some screenshots maybe?) how you have set the hint?

Generally, I hope you already found SAP Note

2509161 - Set SQL Execution Hints in Calculation View via HANA Studio Modeler

which outlines the prerequisites and more details on what to consider when applying hints on view level in the HANA Studio Modeler.

Regards,

Bojan

[former_member667589]

Hello Bojan,

Thanks for your response !

We have put this HINT as below. Motive was to push JOIN before AGGREGATION as it was observed to be performance efficient.

We have used HINT with same approach in other object and there it is working good through both - SQL console & View Properties.

-Mandar.

[Bojan-lv-85]

Hello Mandar,

what about using query_level_sql_hints (lower case) I just tried it on one of my instances and it does make a difference (being fully aware that it should not).

BR, Bojan

[former_member667589]

Hello Bojan,

First of all, sorry for delay in response !

We tried using query_level_sql_hints (lower case) and unfortunately results / performance is same. We did not see any improvement by changing HINT that way.

We think only way out is remodeling our object to get better performance.

-Mandar.

[Bojan-lv-85]

Hello Mandar,

I might have missed something here but afaik whether the hint shows an effect or not was not the initial question. You reproted that the hint is not considered when defined in the view properties. Do yo now see that the hint is applied? (regardless of the performance improved or not)

Regards, Bojan

[former_member667589]

Hello Bojan,

What I meant to say is, though I put query_level_sql_hints in name#value pair in lower case, performance is not optimized and even I do not see that HINT in any plan (explain/visualization). Hope this is what you wanted to check with me.

-Mandar.