Performance Guidelines for ABAP Development on the SAP HANA Database

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

• 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

• 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

• 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

As on all database systems, there is a performance overhead associated with every database access for connection handling, SQL parsing, execution plan determination, etc.

The following existing guidelines should be prioritized higher on SAP HANA:

• For modifying operations (INSERT, UPDATE, DELETE) using array operations should be preferred to single operations when changing many data records
• Nested SELECT loops should be avoided or replaced if possible by
  • Changing the nested single SELECT statement to an appropriate SQL construct (e.g. FOR ALL ENTRIES, JOIN, sub-query, etc.)
  • Avoiding repeated access to the same data via SQL
  • Using the ABAP table buffer (see existing guidelines for the ABAP table buffer)

In most cases, SAP HANA does not require secondary indices for good search performance.

To reduce main memory consumption, and to improve insert performance all existing non-unique secondary database indices on columnar tables are removed during migration or do not get created during installation for all AS ABAP systems from SAP NetWeaver 7.4 onwards.

For some use cases secondary indexes can still be beneficial. This is especially true for highly selective queries on non-primary key fields. These queries can be significantly improved by indexes on single fields which are most selective. SAP Note 1794297 describes the procedure to find and create these indexes.

The guideline is renamed to “Keep load away from the database, but push data-intensive calculations to the database where applicable”

On SAP HANA, it is beneficial to move data-intensive calculations into the database. Nevertheless, it is not recommended to execute the same operations redundantly, e.g. in different user contexts or different dialog steps of the same user. Meaningful buffering of results on the application server should be applied.

The following recommendation should be considered in this light on SAP HANA

Sorting data:In general, the recommendations for sorting remain as before, i.e. if the database does not use the same index for sorting as for selection, then it may be in some situations  more efficient to sort in the application server in particular if all data to be sorted has to be fetched from the application server anyway. However, if the sorting is part of determining the result set (e.g. select top n customers by revenue) or the sorting is part of a larger calculation logic (e.g. within a procedure), it should be done in SAP HANA.