Parallel Cursor – To speed up performance of Nested LOOP

By | December 13, 2009 | Performance | 34,325 | 6

Technique to speed up the performance of the Nested LOOP – Parllel Cursor

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.
*
TYPESty_t_vbak TYPE STANDARD TABLE OF vbak.
DATAit_vbak TYPE ty_t_vbak .
*
TYPESty_t_vbap TYPE STANDARD TABLE OF vbap.
DATAit_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.
*
DATAlv_start_time TYPE timestampl,
      lv_end_time   TYPE timestampl,
      lv_diff       TYPE timestampl.
DATAlv_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.
*
CLEARlv_start_timelv_end_timelv_diff.
*
*....... Parallel Cursor with Nested Loop .......................
* Get the Start Time
GET TIME STAMP FIELD lv_start_time.
*
* Starting the Parallel Cursor
SORTit_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:

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Naimesh Patel{272 articles}

I'm SAP ABAP Consultant for more than a decade. I like to experiment with ABAP especially OO. I have been SDN Top Contributor.
Follow :

Explore all of his 272 articles.

Load comments

6 Comments

  • Anonymous

    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 ?

  • Anonymous

    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 ?

  • Mahendar

    HI Naimesh,
    Very Good Articles from your end ! really appreciated.
    Keep posting .

    Thanks
    Mahendar.G

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.