HANA Performance Guidelines

SQLScript Procedure Vs. Modeling View

Always Prefer Modeling Views over SQLScript Procedure:

Modeling views (Attribute view, Analytic view and Calculation view) are specially designed artifacts for HANA. As a developer you do not need to worry about query optimization and parallelization.

These modeling views are faster than SQLScript procedures almost in all the scenarios. Plus these views are easier for others to understand, remodel and change.

However, there are some complex scenarios which cannot be implemented in modeling views and you need SQLScript procedures for that. But if you do not write it carefully you might end up with inefficient code.
SQLScript should always be second option compare to modeling views.

Performance Guidelines - General Principles

• • Minimize the join operations
  • • Avoid long join chains
  • • Avoid joins between big tables
• • Reduce data amount as early as possible
  • • Use data aggregation
  • • Use data filters where possible
• • Keep the result sets small
  • • Data transfer to the UI clients also requires time
• • Minimize data transfer between the engines
  • • Prefer execution of calculation in lower layers, instead of pushing data up and calculate there
• • Avoid expensive calculation or data manipulation
• • Row based database expressions are expensive
• • Intermediate results needs to be written into buffer and takes additional time
• • Complex expressions, e.g IF, CASE statements
• Let us first reconsider the existing guidelines. In a nutshell, they are usually summarized in the “5 golden rules”:
• 
  

  Icon	Rule	Details / Examples
  	Keep the result sets small	Do not retrieve rows from the database and discard them on the application server using CHECK or EXIT, e.g. in SELECT loops Make the WHERE clause as specific as possible
  	Minimize amount of transferred data	Use SELECT with a field list instead of SELECT * in order to transfer just the columns you really need Use aggregate functions (COUNT, MIN, MAX, SUM, AVG) instead of transferring all the rows to the application server
  	Minimize the number of data transfers	Use JOINs and / or sub-queries instead of nested SELECT loops Use SELECT … FOR ALL ENTRIES instead of lots of SELECTs or SELECT  SINGLEs Use array variants of INSERT, UPDATE, MODIFY, and DELETE
  	Minimize the search overhead	Define and use appropriate secondary indexes
  	Keep load away from the database	Avoid reading data redundantly Use table buffering (if possible) and do not bypass it Sort Data in Your ABAP Programs

Best Practices for Join Operations

• • Joins are well optimized in the join engine. They are normally faster than Joins in the Calculation Engine. JOIN should be performed in Analytic/Attribute view instead of Calculation View (though in some cases that’s the only way).
• • CE_JOIN is limited to join only two tables. Cascaded Joins are very expensive and not always lead to right results.
• • Left outer join is normally faster than inner join.
• • Left outer join is almost equally fast as Referential Join.
• • Currently one single join operation is executed by 1 core at maximum (Multiple Join are parallelized across multiple cores).
• • Avoid joins on calculated fields, e.g. concatenating two fields into one and execute a join on it. Materialize the concatenated field (if not done by the engine)
• • Avoid non-equijoin predicates on column table as column engine does not natively support join predicates other than equality condition.

Best Practices for SQLScript

• • Do not mix up SQL statement and CE_* operator within the same script
• • Query should be formulated in such a way that minimizes data transfer between different engines.
• • Avoid implicit type casting
• • Reduce complexity of SQL Statements. It helps in identifying common sub-expression
• • Reduce dependencies between different SQL statement and use declarative constructs to enable parallel execution of SQL statements
• • Executing dynamic SQL is slow because compile time checks and query optimization must be done for every invocation of the procedure.
• • Avoid CURSOR (and other imperative constructs) as parallelization can’t be done
• • Consider the impact of expensive calculations (like unit and currency conversion)

Reference at:

http://scn.sap.com/community/abap/hana/blog/2013/03/24/performance-guidelines-for-abap-development-on-the-sap-hana-database

•