How to implement "Parallel Cursor"?

Sometimes we need to loop within a loop as other calls it as a nested loop. We cannot deny the fact that there are tables in the data dictionary wherein we loop a header internal table and carry out some processing for each of the lines in the corresponding Item level internal table. To perform the same, we end up using nested loops which have a great impact on the performance and result in long execution times. 

In order to overcome this situation, we need to implement PARALLEL CURSOR technique/approach wherein we maintain a cursor which holds the value of the index from where the subsequent search should start. This will result in vastly optimized ABAP components which would further facilitate faster data loading.


Illustration:

Suppose we have two internal tables IT_EKKO (header) and IT_EKPO (item) containing purchase order header and item level data and we need to extract some item level details on the basis of PO number which is common key in both the tables. We will have to put a nested loop here. Suppose there are 1000 records in table IT_EKPO and for PO 123 there are three records available in IT_EKPO table.

Example of a Nested Loop

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
DATA : 
   it_ekko  TYPE STANDARD TABLE OF ekko,
   wa_ekko  TYPE ekko,
   it_ekpo  TYPE STANDARD TABLE OF ekpo,
   wa_ekpo  TYPE ekpo.

DATA : 
   lv_tabix TYPE sy-tabix.

SELECT * FROM ekko INTO TABLE it_ekko.
SELECT * FROM ekpo INTO TABLE it_ekpo.

LOOP AT it_ekko INTO wa_ekko.
   LOOP AT it_ekpo INTO wa_ekpo WHERE ebeln = wa_ekko-ebeln.
       "Logial ABAP statements 
   ENDLOOP.
ENDLOOP.

Here when WA_EKKO-EBELN =123 the inner loop will execute 1000 times and statement inside the loop will execute 3 times. This will result in higher data load runs and poor performance. We can optimize the above using parallel cursor technique as elaborated below:

Parallel cursor is the technique to increase the performance of the program, when there are nested loops. The approach uses a variable using which we can maintain a cursor which will avoid the search to start all over again, from the first record. Instead it will control the loop to start the search from a specified index. This as a result makes the search faster and thus the high end performance improvement is scene in data loading processes. A mandatory prerequisite before using the approach is that the internal tables are both sorted by the respective key fields.


Nested Loop Using Parallel Cursor:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DATA : 
it_ekko TYPE STANDARD TABLE OF ekko,
wa_ekko TYPE ekko,
it_ekpo TYPE STANDARD TABLE OF ekpo,
wa_ekpo TYPE ekpo.
DATA : lv_tabix TYPE sy-tabix.

SELECT * FROM ekko INTO TABLE it_ekko.
SELECT * FROM ekpo INTO TABLE it_ekpo.
SORT it_ekko BY ebeln.
SORT it_ekpo BY ebeln.

LOOP AT it_ekko INTO wa_ekko.
   READ TABLE it_ekpo TRANSPORTING NO FIELDS 
   WITH KEY ebeln = wa_ekko-ebeln binary search.
   IF sy-subrc EQ 0.
      lv_tabix = sy-tabix.
      LOOP AT it_ekpo INTO wa_ekpo FROM lv_tabix.
         IF wa_ekpo-ebeln NE wa_ekko-ebeln.
            EXIT.
         ENDIF.
         Logial ABAP statements ………..
      ENDLOOP.
   ENDIF.
ENDLOOP.

Here when WA_EKKO-EBELN =123 the inner loop will execute 4 times and statement inside the loop will execute 3 times. This will result in a major performance improvement over the nested loop code elaborated earlier in the document.

No comments:

Post a Comment