PERFORMANCE - SAP HANA Modeling Good Practices....!!!

SAP HANA Modeling Good Practices - Performance.

 

 

• Create all calculations in Analytical or Calculation views. Avoid creating any calculations in Reporting layer (Universe & Front end tools).
• Limit output columns using Projection nodes
• Consider partitioning large tables to get better performance
• Max 2B records per table (or table partition) and max 1000 partitions per table
• Do not create working tables in different schemas. This will create security problems on ownerships. Instead of that create a separate schema and create all working tables and use it in your Modeling.
• Avoid composite primary keys whenever possible. Composite primary key creates additional indexes on the table, which will take additional space and hits the performance. If you have to use it be aware of this fact.
• If possible avoid Joins on Character columns.
• Analyze the performance of the Query/Models using Explain Plan and Visualization Plan
• Identify the long running queries by reviewing Performance tab to analyze system performance located under the Administration editor
• Hana Automatically handles Indexes on key columns. Create secondary index on non-key columns if it is absolutely necessary .Create indexes on non-primary key columns (with high cardinality) to enhance the performance of some queries using the index adviser.

        Syntax:  CREATE INDEX ON .

• Use the index adviser to find out for which tables and columns indexing would be most valuable. The indexAdvisor.py script is part of a SAP HANA system installation and runs from the command line. It is located in the $DIR_INSTANCE/exe/python_support directory.
• Indexing the primary key columns is usually sufficient because queries typically put filter conditions on primary key columns. When filter conditions are on non-key fields and tables have many records, creating an index on the non-primary key columns may improve the performance.
• There is a trade-off between indexing and memory consumption: While indexing non-primary key columns can make query execution faster, the downside is that memory consumption increases. The index adviser takes this trade-off into account: In dynamic mode, the index adviser looks for the tables and columns that are used most often. The higher the selectivity is, that is, the more different values are in the column, the higher are the performance gains from indexing the columns.
• To check whether there is an index for a column, you can see the system view M_INDEXES.
• With SAP HANA, you do not need to perform any tuning to achieve high performance. In general, the SAP HANA default settings should be sufficient in almost any application scenario. Any modifications to the predefined system parameters should only be done after receiving explicit instruction from SAP Support.
• If two columns are frequently compared by queries, ensure the two columns have the same data type. For columns of different types, SAP HANA uses implicit type casting to enable comparison in HANA Models. However, implicit type casting has a negative effect on performance.