ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP - Database Accesses → Open SQL → Open SQL - Overview
Open SQL - Performance Notes
Other versions: 7.31 | 7.40 | 7.54
Keeping the Number of Hits Low
Wherever possible, you should include all selection conditions in the
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.
Use the aggregate functions rather than selecting data and grouping it yourself. SAP buffering is switched off when you use aggregate functions.
UPDATE a database record, you should only update those columns that have been changed.
Use a small number of database accesses
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 ... 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 (
SORTon 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
The advantage of
SELECT ... FOR ALL ENTRIESis 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 ENTRIESif a
JOINis not possible for syntactical reasons or if the
JOINwould result in high redundancy due to the constantly repeated fields from the left table.
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).
You should use explicit cursor handling for large quantities of data and logical databases.
Search small quantities of data
WHEREconditions, you should use EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search.
WHEREcondition are also contained in the index.
Reduce the database load wherever possible
SAP buffering is switched off if the following is specified:
SELECT FOR UPDATEor
SELECT DISTINCTin the
BYPASSING BUFFERin the
JOINs and subqueries (subqueries),
ORDER BY f1 f2 ...in the
aggregate functions in the
IS [NOT] NULLin the
You cannot process a query in the SAP buffer if the generic key section is not specified in the
UPDATE, you should check whether you need to read a selection of entries using
SORT, rather than using the
ORDER BYclause, where the sort is not supported by an index.
DELETE ADJACENT DUPLICATES FROM itabinstead of using
Strings in database tables
Since the data of long strings is stored outside the data record, access to long strings is slower than
to other data types. This applies particularly to set operations. This note is not applicable if you use short strings.