INCLUDE
Open SQL - Performance Notes
Keep the number of hits low
Wherever possible, all selection conditions should be included in the
WHERE clause, using AND and checking for equality. Do not
select a large dataset and then check it with CHECK . If all data
is to be read, no WHERE condition needs to be specified.
Transfer small amounts of data
If only a few fields are used, use SELECT with a structure, not
SELECT * . Alternatively, use one of the
views in ABAP Dictionary to select data.
Use the aggregate functions rather
than selecting data and grouping it independently.
SAP buffering is switched off
when aggregate functions are used.
When UPDATE s are used on a database record, only update those
columns should be updated that were modified.
Use a small number of database accesses
If INSERT , UPDATE , or DELETE are used, sets of data
should be used instead of individual table entries. This ensures that
the index only has to be edited once, which relieves the load on the
database.
Use nested SELECT loops only 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 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. Processing can then be continued 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).
The addition FOR ALL ENTRIES should be used if a JOIN is
not possible for syntactical reasons or if a 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. Parallel cursor processing should be
used only if the outermost table is to be processed completely or to a
large extent, since WHERE conditions for the outermost table
cannot be passed on to other tables (in other words, more data might be
read than is necessary).
Warning: RANGES tables
Explicit cursor handling should be used for large quantities of data and
logical databases .
Search small quantities of data
In WHERE conditions, 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:
SELECT FOR UPDATE or SELECT DISTINCT in the
SELECT clause ,
BYPASSING BUFFER in the FROM clause
,
JOIN s and subqueries ( subqueries ),
ORDER BY f1 f2 ... in the ORDER-BY
clause .
aggregate functions in the
SELECT clause .
IS [NOT] NULL in the WHERE condition .
A query cannot be processed in the SAP buffer if the generic key section
is not specified in the WHERE condition .
Avoid re-reading the same data.
Before a table is modified using DELETE , INSERT , or
UPDATE , a check should be made to see whether a selection of entriesn
needs to be read using SELECT .
If data is to be sorted, 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 duplicates can be deleted using the variant DELETE
ADJACENT DUPLICATES FROM itab instead of using SELECT DISTINCT
.
Logical databases should be used 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 to short
strings.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved