Performance Guidelines for ABAP Development on the SAP HANA Database

[Former Member]

If you are an experienced ABAP Developer, then you most likely know the classical performance guidelines for using Open SQL (if not, then you should look them up immediately).

One of the frequently asked questions we receive is:

“What changes in the context of SAP HANA regarding these guidelines?”

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

Well, the short answer regarding the question above is

• All existing (standard and custom) ABAP code runs on SAP HANA without modifications
• All the existing  guidelines are still valid for SAP HANA as general recommendation

However, the priorities of some rules are changing, i.e. some aspects are less important due to the nature of the In-Memory Column Store but there are also certain patterns of non-optimal coding with higher impact on SAP HANA. Furthermore, there are completely new opportunities for performance tuning on SAP HANA which were not possible in the past, e.g. by pushing complex operations to the database.

In this blog series, hermann.gahm and myself will drill into more details of the slightly adapted performance recommendations, and give some background information. In addition, we try to give some guidance for several frequently asked questions.

The planned structure of this blog series is as follows:

• Performance Guidelines for ABAP Development on the SAP HANA Database (this post)
• Details and background information on adapted guidelines (to come)
• Frequently Asked Questions for ABAP Development on SAP HANA (to come)

The following recommendations are derived from measurements and experiences based on SAP Business Suite using SAP NetWeaver AS ABAP 7.4 running on SAP HANA SPS5. They will be added to the standard ABAP 7.4 documentation and also supported in standard tools such as the ABAP code inspector.

Guideline	Additions in the context of SAP HANA
	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.

As outlined above, the next post contains some more technical background information for the reasoning behind these recommendations, and some answers to frequently asked questions.