In ABAP coding many performance problems are related to the handling of internal tables. Especially in the case of nested loops on two large tables, it is important for the runtime of the inner operation to scale faster than linear, otherwise the complete nested loop would show a quadratic scaling. Therefore, it is essential to know the runtime behavior of internal table operations. In this web log we will present precise measurement results of read statements on internal tables.
Assume your program contains a nested loop on two internal tables itab1 and itab2. The most commonly used ones are the following three,
LOOP AT itab1 INTO wa1.
LOOP AT itab2 INTO wa2.
If ( wa2-key = wa1-key ).
.. processing ..
ENDIF.
ENDLOOP.
ENDLOOP.
Or
LOOP AT itab1 INTO wa1.
LOOP AT itab2 INTO wa2 WHERE key = wa1-key.
.. processing ..
ENDLOOP.
ENDLOOP.
Or
LOOP AT itab1 INTO wa1.
READ TABLE itab2 INTO wa2 WITH key = wa1-key.
IF ( sy-subrc = 0 ).
.. processing ..
ENDIF.
ENDLOOP.
The loop at table itab1 must process all lines. There is nothing to improve, as it will scale linearly with the number of lines in table 1, i.e. with N1. It is obvious that the first example loops over all lines in table itab2, i.e. N2. Altogether the nested loop scales with N1 * N2. If both N1 and N2 grow with the amount of processed data N, which can be the number of positions, contract lines etc., then the nested loop scales proportional to N*N, i.e. it scales quadratically or nonlinearly.
It is often overseen that also examples 2 and 3 scale quadratically, as the ‘LOOP AT WHERE’ and the READ without BINARY SEARCH on a standard table also scan the whole table, i.e. itab2.
This, however, is not necessary, as only one or a few lines of itab2 correspond to one line in itab1. These lines must be found efficiently to get a scaling behavior on N2 which is faster than linear.
LOOP AT itab1 INTO wa1.
Efficient Operation to find the line
or lines on table itab2 which correspond to wa1
ENDLOOP.
The following sections present measurements on different table types. How the measurements must be done in order to get reliable results will be explained in my next web log (coming soon).
The fastest access type is when the searched line can be accessed directly, which makes this read type independent of the size of the internal table. This is achieved by
Figure 1: Averaged runtimes (in microseconds) for different fast reads on internal tables as a function of the table sizes N. Red indicates the read with index on a standard table, blue the read with index on a sorted table, and green the read on a hashed table with a complete table key.
The binary search splits the search area into two halves in every step, and checks in which half it must continue to search. Therefore the runtime must have a logarithmic dependence on the table size N. It is realized by
Figure 2: Averaged runtimes (in microseconds) for different read with logarithmic dependences on table size N. Red indicates the read with binary search on a standard table, and blue the read on a sorted table with a table key.
Reads with keys which are only incomplete table keys can exploit the binary search and the sorted search for the leading fields of the table key. The rest of the processing is a linear scan.
For completeness on nested loop processing a remark on loops as inner operation is necessary. A loop is required if several lines of the inner table 2 can fulfill the key condition. The loop can be realized by
To be more precise the coding is added
It is important that the SORT on table 2 is outside of the LOOP on table 2, so that it is executed only once. The BINARY SEARCH efficiently finds the starting point of the loop, but it is equally important to leave the LOOP as soon as the condition is no longer fulfilled.
For the LOOP AT WHERE on standard table we expect a linear dependence on the size of table 2, as it must scan the whole table. The other two methods should show logarithmic dependencies. Especially the - not so widely known - workaround on standard tables can help to improve existing programs where a change of the table type is no longer possible.
Figure 3: Averaged runtime (in microseconds) for ‘LOOP AT … WHERE’ with different table sizes N. Black is the very expensive loop at where on standard table, whichis already for small values outside of the displayed scale. Blue indicates the loop at where on a sorted table, and red the workaround on a standard using read binary search plus loop from index.
Remarks: The measurements were executed in July 2007 on a pretty fast new machine. Older servers will give larger absolute times; however, the relations should hold on all systems.
Here, reliable measurements of reads and loops on internal tables have been shown. The results are summarized in the table below. Only operations scaling faster than linear should be used inside a nested loop, otherwise quadratic coding is produced.
More information on performance topics can be found in my new textbook on performance (published Nov 2009). However please note, that it is right now only available in German.
Chapter Overview:
In the book you will find detailed descriptions of all relevant performance tools. An introduction to database processing, indexes, optimizers etc. is also given. Many database statements are discussed and different alternatives are compared. The resulting recommendations are supported by ABAP test programs which you can download from the publishers webpage (see below). The importance of the buffers in the SAP system are discussed in chaptr five. Of all ABAP statements mainly the usage of internal tables is important for a good performance. With all the presented knowledge you will able to analyse your programs and optimize them. The performance implications of further topics, such as modularisation, workprocesses, remote function calls (RFCs), locks & enqueues, update tasks and prallelization are explained in the eight chapter.
Even more information - including the test programs - can be found on the webpage of the publisher.
I would recommend you especially the examples for the different database statements. The file with the test program (K4a) and necessary overview with the input numbers (K4b) can even be used, if you do not speak German!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
4 | |
3 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |