Improved version of Parallel Cursor

Problem with previous Method: The disadvantage of the code in my previous post is that we can not use it when the inner loop key fields are are independent of key fields of outer loop. i.e. The assumption was the inner loop index keeps incrementing as the outer loop index index moves ahead. But this is not the case for 90% of our code. For example let outer loop be VBPA table and inner loop be KNA1 table, in which case, the key field for VBPA will be VBELN, but the key field for KNA1 would be KUNNR. Also reader can note it is not necessary that inner loop index gets incremented for each outer loop parse.

Alternate Method: To overcome this problem, we use a READ statement with binary search before the inner loop starts. This gets the starting index of the inner loop and then rest of the logic remains same.

Reader can witness that this methods is very easy and understandable compared to previous code.

Code:

sort: lt_vbpa by vbeln,
      kna1 by kunnr.

loop at lt_vbpa into wa_vbpa.
    read lt_kna1 into wa_kna1
         with key kunnr = wa_vbpa-kunnr
         binary search.
    if sy-subrc = 0.
      v_kna1_index = sy-tabix.
      loop at lt_kna1 into wa_kna1 from v_kna1_index.
        if wa_kna1-kunnr <> wa_vbpa-kunnr.
          exit.
        endif.

****** Your Actual logic within inner loop ******

      endloop. "KNA1 Loop
    endif.
endloop.  " VBPA Loop  

Avoiding Nested Loops Using Parallel Cursors

Background: Performance of program, function modules, BAdi or any piece of code would drastically go down when:

1. Nested loops are used


2. Corresponding internal tables are very huge


3. When child table is larger than the parent table

Statistics:
Sample program run with different number of BKPF and BSEG tables entries is shown below:

Observation:
One can observe that as the data increases, the time taken for nested loop increases drastically, at the same time, Parallel cursor method did not have any considerable time.

Verdict: Use parallel cursor method whenever there is a need to have nested loop.

Method:
Assume you fill up bkpf_tab and bseg_tab.
Code for normal nested loop: ( I consider this as UGLY code from now onwards)

loop at bkpf_tab into bkpf_lin.
    bkpf_reads = bkpf_reads + 1.
    loop at bseg_tab into bseg_lin where
        bukrs = bkpf_lin-bukrs and
        belnr = bkpf_lin-belnr and
        gjahr = bkpf_lin-gjahr.
        bseg_reads = bseg_reads + 1.
    endloop.
endloop.

Code for Parallel Cursor:

SORT: bkpf_tab BY bukrs belnr gjahr,
      bseg_tab BY bukrs belnr gjahr.
bseg_index = 1.
LOOP AT bkpf_tab INTO bkpf_lin.
    bkpf_reads = bkpf_reads + 1.
    LOOP AT bseg_tab INTO bseg_lin FROM bseg_index.
      IF bseg_lin-bukrs <> bkpf_lin-bukrs OR
         bseg_lin-belnr <> bkpf_lin-belnr OR
         bseg_lin-gjahr <> bkpf_lin-gjahr.
          bseg_index = sy-tabix.
          EXIT.
      ELSE.
          bseg_reads = bseg_reads + 1.
      ENDIF.
    ENDLOOP.
ENDLOOP.

Go through these code and post your comments

Analysis:
In the so called UGLY code, for every record in parent table (BKPF), the whole child table (BSEG) is searched and hence the delay. In Parallel cursor code both the tables are sorted and child table is not accessed completely, instead the index is stored in every iteration of the child table, once all the child records for current parent record is over, the index of the current child record is used for accessing first child record of next parent entry without searching the entire table. This is doen using 'FROM' clause of the loop. The where condition for the child loop should be avoided at any cost. Store the index and use the same for the next parent record. As the tables are sorted, this method should work excellently.

Going forward:
I would add a checkilist item for my code review to check for Parallel cursor for any place where a Nested loop is involved.