Today, we will tackle down the biggest performance related issue around the Nested Loops.
Preface
Traditionally in ABAP, we use the LOOP using the WHERE clause for Nested loops. This type of nested loops are very common in our day-to-day programming. But, the cost, in terms of performance, is higher when we use the nested loops. This cost would become a key issue when working with huge tables e.g. BKPF & BSEG, VBAK & VBAP, MKPF & MSEG. Sometimes, this cost increases and reaches to the point where program fails to finish the execution.
We have the concept of Parallel Cursor exists in ABAP to overcome this hurdle and reduce this cost. In parallel cursor, we first try to see if there is any entry exist in the second table inside the LOOP construct of first table. We use the READ .. WITH KEY .. BINARY SEARCH to check if the entry exist in the second table. We use this record number SY-TABIX to LOOP on the second table using LOOP .. FROM index.
This code snippet gives us the idea of the time taken by both the nested loops and the parallel cursor loops.
Code Snippet
In this code snippet, I have “classical” nested loop and the Parallel Cursor nested loop. You would notice that, by using Parallel cursor, you can improve performance a lot.
*&---------------------------------------------------------------------*
*& Report ZTEST_NP_PARALLEL_CURSOR
*& Purpose: Illustration on how to use Parallel Cursor
*&---------------------------------------------------------------------*
*
REPORT ztest_np_parallel_cursor.
*
TYPES: ty_t_vbak TYPE STANDARD TABLE OF vbak.
DATA: it_vbak TYPE ty_t_vbak .
*
TYPES: ty_t_vbap TYPE STANDARD TABLE OF vbap.
DATA: it_vbap TYPE ty_t_vbap.
*
FIELD-SYMBOLS: <lfs_vbak> LIKE LINE OF it_vbak,
<lfs_vbap> LIKE LINE OF it_vbap.
*
* necessary data selection
SELECT * FROM vbak
INTO TABLE it_vbak
UP TO 1000 ROWS.
CHECK it_vbak IS NOT INITIAL.
SELECT * FROM vbap
INTO TABLE it_vbap
FOR ALL ENTRIES IN it_vbak
WHERE vbeln = it_vbak-vbeln.
*
DATA: lv_start_time TYPE timestampl,
lv_end_time TYPE timestampl,
lv_diff TYPE timestampl.
DATA: lv_tabix TYPE i.
*
*...... Normal Nested Loop .................................
* Get the Start Time
GET TIME STAMP FIELD lv_start_time.
*
* Nested Loop
LOOP AT it_vbak ASSIGNING <lfs_vbak>.
LOOP AT it_vbap ASSIGNING <lfs_vbap>
WHERE vbeln = <lfs_vbak>-vbeln.
ENDLOOP.
ENDLOOP.
*
* Get the end time
GET TIME STAMP FIELD lv_end_time.
*
* Actual time Spent:
lv_diff = lv_end_time - lv_start_time.
WRITE: /(50) 'Time Spent on Nested Loop', lv_diff.
*
CLEAR: lv_start_time, lv_end_time, lv_diff.
*
*....... Parallel Cursor with Nested Loop .......................
* Get the Start Time
GET TIME STAMP FIELD lv_start_time.
*
* Starting the Parallel Cursor
SORT: it_vbak BY vbeln,
it_vbap BY vbeln.
LOOP AT it_vbak ASSIGNING <lfs_vbak>.
*
* Read the second internal table with BINARY SEARCH
READ TABLE it_vbap TRANSPORTING NO FIELDS
WITH KEY vbeln = <lfs_vbak>-vbeln
BINARY SEARCH.
* Get the TABIX number
lv_tabix = sy-tabix.
* Start the LOOP from the first accessed record in
* previous READ i.e. LV_TABIX
LOOP AT it_vbap FROM lv_tabix ASSIGNING <lfs_vbap> .
*
* End the LOOP, when there is no more record with similar key
IF <lfs_vbap>-vbeln <> <lfs_vbak>-vbeln.
EXIT.
ENDIF.
* Rest of the logic would go from here...
*
ENDLOOP.
*
ENDLOOP.
*
* Get the end time
GET TIME STAMP FIELD lv_end_time.
*
* Actual time Spent:
lv_diff = lv_end_time - lv_start_time.
WRITE: /(50) 'Time Specnt on Parallel Cursor Nested loops:', lv_diff.
Some statistics
I ran this program multiple times and capture this statistics.
More Performance Tuning tips in ABAP
Check out the other threads to learn more Performance Tuning in ABAP:
- Parallel Cursor – To speed up performance of Nested LOOP
- Parallel Cursor – 2: without using READ
- Use of Field-symbols vs Work area
- Measure the Performance between Break-Points using SE30
- READ-ONLY attribute vs GETTER methods
- Performance of ITAB Copy
- Performance of Using Keys in SELECT with FOR ALL ENTRIES
- ABAP Internal Table Performance for STANDARD, SORTED and HASHED Table
- ABAP Internal Table Secondary Key Performance comparison
- ABAP build a table with Unique Keys – Performance Comparison
- ABAP Parallel Cursor – Things to Remember
- Use of REFERENCE variable vs Workarea vs Field-Symbols
- FOR ALL ENTRIES β Why you need to include KEY fields
- ABAP Performance for DELETE on ITAB
- Write Green Programs
Loading ...
Hi Naimesh,
i appreciate your articles but exist a better method ( how suggest in tip & tricks work bench)
i type i
sort tab_a by key
sort tab_b by key key1
loop at tab_a.
loop at tab_b from i.
if tab_b-key = tab_a-key.
….
elseif tab_b-key > tab_a-key.
i = sy-tabix.
exit.
endloop.
endloop.
from theory:
table a with m records
table b with n records
1) simple search with nested loop
the cost is order of m * n/2
2) loop at first and binary search
the cost is order of m * log2(n)
3) Nested loop with index
the cost is order of m + n
Ciao
Hello Anonymous,
i appreciate your articles but exist a better method ( how suggest in tip & tricks work bench)
This parallel cursor technique is covered in the second part of the parallel cursor technique.
Parallel Cursor – 2: without using READ
Regards,
Naimesh Patel
Hi Naimesh,
Using sorted table with suitable keys or parallel cursors.
Which is better ?
ahhh these are the standards we have been using for 5 years, i think someone started newly working for TOTAL π
I have a question on parallel cursor technique with 3 internal tables or more..
how to work on it and can u please give some examples to do it ?
HI Naimesh,
Very Good Articles from your end ! really appreciated.
Keep posting .
Thanks
Mahendar.G