performance tuning for Nested loops

[Former Member]

Hi,

Iam having performance problem when running the below code. I have used nested loops and the outer loop have around 60,000 records and inner loop around 90,000 records. When I run the job its taking more than a day to complete. Can any one suggest how to improve the below code. Please dont give any docs or tutorials but suggest the improvement in below code.

___________________________________________________________________________________

DATA:w_output1 TYPE ty_output.

t_final_zuonr[] = t_output[].

SORT t_output BY bukrs zuonr.

DELETE ADJACENT DUPLICATES FROM t_final_zuonr COMPARING bukrs zuonr.

MESSAGE 'To find the categories based on zuonr(assignement number)' TYPE 'S'.

LOOP AT t_final_zuonr INTO w_output1.

LOOP AT t_output INTO w_output WHERE bukrs EQ w_output1-bukrs

AND zuonr EQ w_output1-zuonr.

IF w_output-augbl <> space.

w_output-rlc = '1'.

MODIFY t_output FROM w_output TRANSPORTING rlc WHERE bukrs = w_output-bukrs AND

zuonr = w_output-zuonr.

ENDIF.

IF w_output-rlc IS INITIAL.

CASE w_output-blart.

WHEN 'WE'.

v_blart = 'X'.

WHEN 'RE' OR 'RN'.

v_blart1 = 'X'.

ENDCASE.

IF v_blart = 'X'.

v_sum_3 = v_sum_3 + w_output-dmbtr.

ENDIF.

IF v_blart = 'X' AND

v_blart1 = 'X'.

w_output-rlc = '2'.

ELSEIF v_blart1 = 'X' AND

v_blart = ' ' AND

w_output-dmbtr GE 0.

w_output-rlc = '4'.

ELSEIF v_blart1 = 'X' AND

v_blart = ' ' AND

w_output-dmbtr LE 0.

w_output-rlc = '5'.

ELSEIF v_blart = 'X' AND

v_sum_3 LE 0.

w_output-rlc = '3'.

ELSEIF v_blart = 'X' AND

v_sum_3 GT 0.

w_output-rlc = '6'.

ELSE.

w_output-rlc = '7'.

ENDIF.

ENDIF.

ENDLOOP.

CLEAR :v_blart,

v_blart1.

MODIFY t_output FROM w_output TRANSPORTING rlc WHERE bukrs = w_output-bukrs AND

zuonr = w_output-zuonr.

REFRESH t_zuonr.

ENDLOOP.

REFRESH t_final_zuonr.

MESSAGE 'Rlc category updated to output table' TYPE 'S'.

Best Regards

Suresh

_____________________________________________________________________________________

[Former Member]

Hi,

Avoid nested loops is in your code,

Instead u can use read statment like follows,

LOOP AT t_final_zuonr INTO w_output1.

READ t_output INTO w_output WITH KEY bukrs = w_output1-bukrs.

ENDLOOOP.

it improve your code performanace..

reward points if useful,

seshu.

[Former Member]

hi suresh

your code can be modified as

SORT t_output BY bukrs zuonr.

DELETE ADJACENT DUPLICATES FROM t_final_zuonr COMPARING bukrs zuonr.

LOOP AT t_final_zuonr INTO w_output1.

read table t_output INTO w_output with key bukrs EQ w_output1-bukrs

AND zuonr EQ w_output1-zuonr

binary search.

if sy-subrc = 0 .

• your code

endif .

endloop .

donot forgot to reward points if helpful

regards

hitesh

[former_member194613]

LOOP AT t_final_zuonr INTO w_output1.

LOOP AT t_output INTO w_output WHERE bukrs EQ w_output1-bukrs

AND zuonr EQ w_output1-zuonr.

Your problem is a well known problem, the LOOP AT WHERE on a standard table must loop each time the full table,

as I can not know whether the table is sorted, either use sorted table or read binary search loop from index exit, or

a read if you need only one record from the inner table.

Please find the details in that blog: Measurements on internal tables: Reads and Loops:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

especially the exact description of 'read binary search - loop from index -exit'

With that changes your performance will go down from 60.000 * 90.000 = 5.400.000.000 to something like 300.000.

Siegfried

[Former Member]

Hi,

You try following code(or logic) and you can decrease condition statment, just try.

DATA:w_output1 TYPE ty_output.

t_final_zuonr[] = t_output[].

SORT t_output BY bukrs zuonr.

DELETE ADJACENT DUPLICATES FROM t_final_zuonr COMPARING bukrs zuonr.

MESSAGE 'To find the categories based on zuonr(assignement number)' TYPE 'S'.

LOOP AT t_final_zuonr INTO w_output1.

LOOP AT t_output INTO w_output WHERE bukrs EQ w_output1-bukrs

AND zuonr EQ w_output1-zuonr.

IF w_output-augbl space.

w_output-rlc = '1'.

zuonr = w_output-zuonr.

ENDIF.

IF w_output-rlc IS INITIAL.

CASE w_output-blart.

WHEN 'WE'.

v_blart = 'X'.

WHEN 'RE' OR 'RN'.

v_blart1 = 'X'.

ENDCASE.

IF v_blart = 'X'.

v_sum_3 = v_sum_3 + w_output-dmbtr.

ENDIF.

IF v_blart = 'X' AND

v_blart1 = 'X'.

w_output-rlc = '2'.

ELSEIF v_blart1 = 'X' AND

v_blart = ' ' AND

w_output-dmbtr GE 0.

w_output-rlc = '4'.

ELSEIF v_blart1 = 'X' AND

v_blart = ' ' AND

w_output-dmbtr LE 0.

w_output-rlc = '5'.

ELSEIF v_blart = 'X' AND

v_sum_3 LE 0.

w_output-rlc = '3'.

ELSEIF v_blart = 'X' AND

v_sum_3 GT 0.

w_output-rlc = '6'.

ELSE.

w_output-rlc = '7'.

ENDIF.

ENDIF.

MODIFY t_output FROM w_output

ENDLOOP.

CLEAR :v_blart,

v_blart1.

zuonr = w_output-zuonr.

ENDLOOP.

REFRESH t_final_zuonr.

MESSAGE 'Rlc category updated to output table' TYPE 'S'.

L.Velu

[dev_parbutteea]

Hi ,

try this, it is simple and it will work:

data: lv_index type sy-tabix.

sort t_output by bukrs zuonr.

LOOP AT t_final_zuonr INTO w_output1.

read table t_output with key bukrs = w_output1-bukrs

zuonr = w_output1-zuonr.

transporting no fields

binary search.

check sy-subrc = 0.

lv_index = sy-tabix

LOOP AT t_output into w_output from lv_index.

check: w_output-bukrs = w_output1-bukrs,

w_output-zuonr = w_output1-zuonr.

.

.

.

Endloop

endloop.

Regards,

Sooness.

Edited by: Sooness Munogee on Jan 17, 2008 6:53 AM

[Former Member]

hi suresh,

Nested Loops - This is one of the fear factors for all the ABAP developers as this consumes lot of program execution time. If the number of entries in the internal tables is huge, then the situation would be too worse. The solution for this is to use parallel cursor method whenever there is a need for Nested Loop.

Program using Normal Nested Loop:

REPORT ZNORMAL_NESTEDLOOP.

TABLES:

likp,

lips.

Data:

t_likp type table of likp,

t_lips type TABLE OF lips.

data:

W_RUNTIME1 TYPE I,

W_RUNTIME2 TYPE I.

START-OF-SELECTION.

select *

from likp

into table t_likp.

select *

from lips

into table t_lips.

get RUN TIME FIELD w_runtime1.

loop at t_likp into likp.

loop at t_lips into lips where vbeln eq likp-vbeln.

endloop.

endloop.

get RUN TIME FIELD w_runtime2.

w_runtime2 = w_runtime2 - w_runtime1.

write w_runtime2.

Nested Loop using Parallel Cursor:

REPORT zparallel_cursor2.

TABLES:

likp,

lips.

DATA:

t_likp TYPE TABLE OF likp,

t_lips TYPE TABLE OF lips.

DATA:

w_runtime1 TYPE i,

w_runtime2 TYPE i,

w_index LIKE sy-index.

START-OF-SELECTION.

SELECT *

FROM likp

INTO TABLE t_likp.

SELECT *

FROM lips

INTO TABLE t_lips.

GET RUN TIME FIELD w_runtime1.

SORT t_likp BY vbeln.

SORT t_lips BY vbeln.

LOOP AT t_likp INTO likp.

LOOP AT t_lips INTO lips FROM w_index.

IF likp-vbeln NE lips-vbeln.

w_index = sy-tabix.

EXIT.

ENDIF.

ENDLOOP.

ENDLOOP.

GET RUN TIME FIELD w_runtime2.

w_runtime2 = w_runtime2 - w_runtime1.

WRITE w_runtime2.

Iteration No Normal Nested Loop Using Parallel Cursor 1. 34,796,147 63,829

2. 38,534,583 56,894

3. 34,103,426 50,510

Hope this is helpful, Do reward.

Edited by: Runal Singh on Jan 17, 2008 3:52 PM