How to improve performance while using tables

Keep the hit list small

Wherever possible, you should include all selection conditions in the WHERE clause, using AND and checking for equality. Do not select a large dataset and then check it with CHECK. If you want to read the whole table, you do not have to specify a WHERE condition at all.


Transfer small amounts of data

If you only want to transfer a few fields, use SELECT with a structure, not SELECT *. Alternatively, you can use one of the views in the ABAP Dictionary to select data. If you do use a view, the SAP buffering is switched off.

You should use the aggregate functions rather than selecting data and grouping it yourself. SAP buffering is switched off when you use aggregate functions.

When you UPDATE a database record, you should only update those columns that have been changed.


Use a small number of database accesses

When you INSERT, UPDATE or DELETE, you should use sets of data instead of individual table entries. This ensures that the index only has to be maintained once, which relieves the load on the database.

You should only use nested SELECT loops when the hit list in the outermost level is very small. There are various ways of avoiding nested SELECT loops:

Building a JOIN in the FROM clause

Joins as views defined in the ABAP Dictionary.

SELECT ... FOR ALL ENTRIES

In the outermost loop, the database table (PACKAGE SIZE) is read section-by-section into an internal table, sorted by its primary key (SORT on the internal table, or read in using ORDER BY PRIMARY KEY). For each data record in the internal table, all associated, dependent records are read into a further internal table (using SELECT ... FOR ALL ENTRIES). This is also sorted. You can then carry on processing using a nested LOOP.
The advantage of SELECT ... FOR ALL ENTRIES is that it provides good performance regardless of the selectivity of the condition on the outermost table, since, in contrast to the nested SELECT, it works in a data-oriented way in the database, but still only picks out the relevant database entries (different to parallel cursor processing).
You should use the addition FOR ALL ENTRIES if a JOIN is not possible for syntactical reasons or if the JOIN would result in high redundancy due to the constantly repeated fields from the left table.

Explicit cursor handling (OPEN CURSOR [WITH HOLD]...)

In this processing type, a separate cursor is opened for each table involved. These are processed in parallel. In order for the system to recognize control breaks, the tables must be sorted ( ORDER BY PRIMARY KEY) before being read. You should only use parallel cursor processing when you want to process the outermost table completely or to a large extent, since WHERE conditions for the outermost table cannot be passed on to other tables (in other words, you might read more data than is necessary).
Caution: RANGES tables
You should use explicit cursor handling for large quantities of data and logical databases.


Search through small amounts of data


In WHERE conditions, you should use EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search.
NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.


Reduce the database load where possible


SAP table buffering

The SAP buffering is switched off:
When you use SELECT FOR UPDATE or SELECT DISTINCT in the SELECT clause,
When you use BYPASSING BUFFER in the FROM clause,
When you use JOINs and subqueries
When you use ORDER BY f1 ... fn in the ORDER-BY clause.
When you use aggregate functions in the SELECT clause.
When you use IS [NOT] NULL in the WHERE condition.

You cannot process a query in the SAP buffer if the generic key section is not specified in the WHERE condition
Avoid re-reading the same data.
Before you change a table using DELETE, INSERT or UPDATE, you should check whether you need to read entries using SELECT.
If you wannt to sort data, it is more efficient to read them into the internal table and sort them using SORT than to use the ORDER-BY clause, where the sort is not supported by an index.
You should check whether you can delete duplicates using the DELETE ADJACENT DUPLICATES FROM itab. instead of using SELECT DISTINCT.
You should use logical databases if possible.

Indexes


Indexes - Form and Use


Indexes help to speed up selection from the database. They consist of a sorted copy of certain database table fields.

The primary index is always created automatically in the SAP System. It consists of the primary key fields of the database table, and there is at most one record in the table matching each possible combination of these fields. This kind of index is called a UNIQUE index.

If you cannot use the primary index to determine a selection result (for example, WHERE condition may not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

However, you should not define an index for all possible fields in the WHERE condition.


Creating an index


You can create an index in Transaction SE11 by choosing Change → Indexes... → Create. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". You then need to save and activate the index.


When to create an index


It is worth creating an index when:
You want to select table entries based on fields that are not contained in an index, and the response times are very slow.
The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.
The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.
The database table is accessed mainly for reading entries.

Using an index consisting of several fields


Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.



Optimal number of fields for an index


An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation.



Fields to include in an index


Include fields that are often selected and have a high selectivity. In other words, you need to check the proportion of the table entries that can be selected with this field. The smaller the proportion, the more selective the field. You should place the most selective fields at the beginning of the index.
If all of the fields in a SELECT statement are contained in the index, the system does not access the data a second time following the index access. If there are only a few fields in the SELECT statmeent, you can improve performance significantly by including all of these fields in the index.
You should not include a field in an index if its value is initial for most of the table entries.

Optimal number of indexes for a table


You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.
The amount of data increases.
The optimizer has too many chances to make mistakes by using the 'wrong' index.


If you are using more than one index for a database table, ensure that they do not overlap.



Avoiding OR conditions


The optimizer generally stops if the WHERE condition contains an OR expression. In other words, it does not evaluate the fields in the OR expression with reference to the index.
An exception to this are OR statements standing on their own. Try to reformulate conditions containing an OR expression for one of the indexed fields. For example, replace:

SELECT * FROM SPFLI
WHERE CARRID = 'LH'
AND (CITYFROM = 'FRANKFURT' OR CITYFROM = 'NEW YORK').

with:

SELECT * FROM SPFLI
WHERE (CARRID = 'LH' AND CITYFROM = 'FRANKFURT')
OR (CARRID = 'LH' AND CITYFROM = 'NEW YORK').


Problems with IS NULL


The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.