Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Open SQL →  Open SQL - Overview →  Open SQL - Performance Notes 

Performance Notes for Individual Open SQL Statements

Other versions: 7.31 | 7.40 | 7.54

SELECT

Specification of the resulting set

  • If the additions SINGLE FOR UPDATE and DISTINCT are used, SAP buffering is skipped.
  • The addition DISTINCT requires sorting in the database server and should therefore be specified only if you can expect duplicates.
  • If aggregate functions are specified, SAP buffering is skipped.
  • Since some database systems do not maintain the number of table rows in their catalog and therefore need to acquire these with a lot of effort, the function COUNT( * ) is not a good one to check whether a table contains a row at all. For this purpose, it is best to use SELECT f ... FROM tab UP TO 1 ROWS for an arbitrary table field f.
  • If only certain columns of a database table are selected, it is recommended that you specify a list of fields in the SELECT clause, or use a view.

    • target area

    Specification of the target area

  • Whether data should better be read into an internal table or a work area depends on the type of further processing: If data is required only once in a program, it should be imported into a work area, row by row, with a SELECT-ENDSELECT loop. Reading data into an internal table requires more memory space (without the disadvantage) because of a considerably higher reading speed. If, on the other hand, data is required many times in a program, it should be read into an internal table. The disadvantage of the increased memory requirement is more than compensated for here through the advantage of a once-off selection.
  • If data is to be imported into an internal table, it is better to import it once into an internal table than to put it, row by row, into a work area and then finally add it to an internal table using APPEND.
  • The variants ... INTO CORRESPONDING FIELDS OF wa, ... INTO CORRESPONDING FIELDS OF TABLE itab, and ... APPENDING CORRESPONDING FIELDS OF TABLE itab require - in comparison to the respective variants without CORRESPONDING FIELDS - a longer runtime (a runtime, however, that is independent of the size of the solution set). It should therefore be avoided, if possible.

    • Specification of the WHERE condition

  • All conditions should be specified in the WHERE clause. In this way, you prevent data from being transported uselessly across the network and (for example, using CHECK) having to be sorted again.
  • For frequently used SELECT statements, an index should be created. In the WHERE clause, the fields of the index must be specified through a logical AND, linked with comparisons for equality. All the fields of an index that are behind a field for which a comparison other than EQ (=) is specified in the WHERE clause cannot be used for searching in the index.
  • The logical NOT in a WHERE clause cannot be supported by an index. For example, WHERE fldate >= '20010228' is better than WHERE NOT fldate < '20010228'.
  • The SAP buffer does not support the condition IS NULL. Therefore, each SELECT command on a buffered table or a view with fields from buffered tables, where the command contains ... WHERE f IS [NOT] NULL, behaves as if the addition BYPASSING BUFFER were specified in the FROMclause.
  • With the dynamic specification of a condition, the syntax check can take place only at runtime. Therefore, the specification of a logical condition at runtime needs more execution time than a corresponding specification in the program text.
  • Searching for variable names in the runtime environment requires a lot of effort. If an ABAP variable is used as a value, the definition of these variables should take place with the dynamic specification of a condition - if possible, in the current context. .

    • Grouping Rows

    If aggregates and groups are created already in the database system and not later by the application server, this can considerably reduce the data quantity that needs to be transported from the database to the application server.

    Sorting Rows

  • In contrast to ... ORDER BY PRIMARY KEY, ... ORDER BY f1 f2 ... is not automatically supported by a (sorted) index. Without a corresponding index, the resulting set must be sorted at runtime. On the basis of SAP architecture, this should not take place on the database server, but on the application server. If it is not appropriate to create a corresponding index, the resulting set should therefore not be sorted on the database server using ... ORDER BY f1 f2 ..., but on the application server using SORT.
  • In the case of larger datasets, the variant ... ORDER BY f1 f2 ... should only be used if the sequence of database fields f1 f2 ... corresponds exactly to the sequence of an index.
  • In contrast to ... ORDER BY PRIMARY KEY, ... ORDER BY f1 f2 ... skips the SAP table buffering.

    • INSERT, UPDATE, MODIFY, DELETE

    If several rows of a database table are to be changed, quantity accesses generally give better performance than individual accesses.