Skip to content

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 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.

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).
    Warning: RANGES tables
    You should use explicit cursor handling for large quantities of data and logical databases.

    • Search small quantities 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 wherever possible

  • Saving database in local buffers (see SAP buffering) can save considerable time in client-server environments, as the access time via the network is considerably higher than access time via a locally buffered table.

    SAP buffering is switched off if the following is specified:
    1. SELECT FOR UPDATE or SELECT DISTINCT in the SELECT clause,
    2. BYPASSING BUFFER in the FROM clause,
    3. JOINs and subqueries (subqueries),
    4. ORDER BY f1 f2 ... in the ORDER-BY clause.
    5. aggregate functions in the SELECT clause.
    6. 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 WHEREcondition.
  • Avoid re-reading the same data.
  • Before you change a table using DELETE, INSERT or UPDATE, you should check whether you need to read a selection of entries using SELECT.
  • If you want to sort data, it is more efficient to read it into the internal table and sort it using SORT, rather than using the ORDER BY clause, where the sort is not supported by an index.
  • Check whether you can delete duplicates using the variant DELETE ADJACENT DUPLICATES FROM itab instead of using SELECT DISTINCT.
  • You should use logical databases if possible.

    • 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.

    Further Help

    Performance Notes

    Continue

    Performance Notes for Individual Open SQL Statements