Performance tuning of HANA models/Views

[Former Member]

Hello Experts,

Could you please suggest what are the different ways in which HANA models can be tuned or optimised for better perfomance.

Please provide the solution with some practical examples.

I need to improve the performance of my one of the Graphical Cal. view.

Kindly help asap.

Thanks

Bishant Kumar

[gopinath_kolli]

Hi Bishant,

Please read this document as well.

Thank you,

Gopi.

[Former Member]

Hi Bishant,

I totally agree with Gowthami on the point that there are no 'fixed steps/methods'

I recently worked on one of the complex model ( has around 40-50 different calculations at different levels, two snapshot tables having 150 million records put together used 4-5 times), where we eventually reduced the server execution from 11s to around 2s.

Our landscape is near real time and we do not have any ETL in place.

Some of my inputs based on the extensive work on the performance optimization on these models ( out final model consumed 8 intermediate models)

1. Filter the data at the base models and try to reduce the data set to what is really needed.

2. Avoid the isnull check before filtering the data. Isnull checks are expensive on large data set. ( we filtered the data set, and then applied isnull check and this helped)

3. Avoid aggregation nodes unless really needed.

4. push the custom calculation to the top most nodes as possible ( we did this and this helped us reduce some time)

5. Apply optimize join and cardinality in each join.

6. we tried with hints like "Calc_View_Unfolding" and we did not see significant improvement in our model.

8. We tried partition on table ( Hash and Range) and we observed that without partition performance was better

9. Avoid network transfer, avoid row search as much possible ( some of the calculation features are not supported by the column engine and you might want to dig little deeper on this) more information

Autodidact: Features of the Column Storage Engine (HANA)    and SAP Note 2223597

10. We tried indexing on snapshot table and this helped.

Hope this helps a bit.

Regards,

Aravind.

[Former Member]

Hi Arvind

Thanks for the help. Need more clarification for some of the points.

3. Avoid aggregation nodes unless really needed. - please explain how does this point helps in performance improvement.

5. What is optimize join. Is this some different types of join.

9. avoid row search. you mean to say in calculated column we have option to select column engine or SQL  engine. so better to go for column engine.Please clarify.

Thanks

Bishant

[gopinath_kolli]

Hi bishant,

3.Aggregation nodes will do aggregations on each and every measure field based on all the attribute fields in that node, so an unwanted aggregated node means an extra work.

5.Optimize join is not some type of join, he might be saying to use joining of tables in an optimized way(For ex, some times one table is joined multiple times. If there is a chance please avoid these kind of things). And cardinality is known if we apply the cardinality the join wil be in optimized state.

9.I think this is searching a table based on a row, which consumes a huge amount of time.

     For Ex:- select A,B,C,D,E,F from X where (A,B,C,D,E,F) IN ( select A,B,C,D,E,F from Y)

Hope this helps.

[former_member208449]

Hello Bishant

I understand your curiosity in trying to optimize one of your views . Being involved in HANA for a couple of years has taught me that there isn't a 'single method' that solves all your performance problems. It all varies on the scenario and the properties selected.

Check these blogs out -

http://www.hdespot.com/wp-content/uploads/2015/11/SAPHDE_Webinar-The-Art-Science-of-Tuning-HANA-Mode...

My personal tips -

1)Get to know planViz and analyze the views using it.

2)Check for performance from the initial nodes itself instead of struggling at the top.

3)Always check for the number of records in the table and structure your view depending on it. Also decide the order in which you join the tables (i.e A->B & C or B->A & C, etc)

4)You can as well set cardinality to improve the performance.

If you need practical solutions here, you will have to show us your model.