Development Guidelines ABAP on HANA

ABAP on SAP HANA: Building business application optimized for In-memory.

To leverage the strengths of SAP HANA from a development perspective, applications follow the “code to data” or so called “code-push-down” paradigm in which data intense computations and calculations logic is pushed down from the application server to the database server.

Example for a simple report: 

In general all code that uses OPEN SQL will benefit already automatically. Therefore it’s also strongly recommended to apply high coding standards/ development guidelines, like the “5 Golden SQL rules” provided by SAP or new OPEN SQL features. However it could be that more performance improvement is required by the business. HANA specific optimization techniques like AMDP (ABAP Managed Database Procedure) or CDS (Core Data Services) views might be required to implement.

5 Golden SQL rules

The next “5 Golden SQL rules” are applicable for development on any DB. However some rules will have more impact on HANA than others.

1. Keep the result sets small

You should aim to keep the result set small. This reduces both the amount of memory used in the database system and the network load when transferring data to the application server. To reduce the size of your result sets, use the WHERE and HAVING clauses.

2. Minimize the amount of transferred data

Data is transferred between the database system and the application server in blocks. To minimize the network load, you should transfer as few blocks as possible. Open SQL allows you to do this as follows:

• Restrict the Number of Lines

1. If you only want to read a certain number of lines in a SELECT statement, use the UP TO          <n> ROWS addition in the FROM clause.
2. If you expect the WHERE clause to return a large number of duplicates entries, you can use      the DISTINCT addition in the SELECT clause.

• Restrict the Number of Columns

1. Do not use * to select all columns unless you really need them. You should only read the columns from a database table that you actually need in the program.

• Use Aggregate Functions

1. If you only want to use data for calculations, it is often more efficient to use the aggregate functions of the SELECT clause than to read the individual entries from the database and perform the calculations in the ABAP program.

• Data Transfer when Changing Table Lines

1. When you use the UPDATE statement to change lines in the table, you should use the WHERE clause to specify the relevant lines and then SET statements to change only the required columns.

3. Minimize the number of data transfers

In every Open SQL statement, data is transferred between the application server and the database system. Furthermore, the database system has to construct or reopen the appropriate administration data for each database access. You can therefore minimize the load on the network and the database system by minimizing the number of times you access the database.

• Multiple Operations Instead of Single Operations

1. When you change data using INSERT, UPDATE, and DELETE, use internal tables instead of single entries.

• Avoid Repeated Access

1. Avoid accessing the same data more than once (for example, SELECT before an UPDATE).

• Avoid Nested SELECT Loops

1. A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. You should therefore only use nested SELECT loops if the selection in the outer loop contains very few lines.

4. Minimize the search overhead

To minimize the size of the result set use the WHERE and HAVING clauses. To increase the efficiency of these clauses, formulate them to fit with the database table indexes.

In case of highly selective queries on non-primary key fields, a secondary index could be beneficial. Some key points to keep in mind:

• Create the index on a large table if access is still slow.
• Create inverted indexes on the most selective columns.
• Create composite indexes on the most selective column combinations
• Indexes have maintenance costs, especially composite indexes. Avoid this as much as possible.

5. Keep load away from the database

Unlike application servers and presentation servers, there is normally only one database server per SAP instance and applications should be written to reduce the database load as much as possible.

Buffers:

• Cross-user buffers: Shared objects, shared buffer, shared memory table buffer.
• User-specific buffers: SAP memory, ABAP memory, program-specific buffering in internal tables.