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
_____________________________________________________________________________________
6 Answers
-
Best Answer
Former MemberPosted on Jan 17, 2008 at 10:21 AMhi 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
Add a comment
-
Former MemberPosted on Jan 14, 2008 at 06:59 AM
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.
Add a comment
-
Former MemberPosted on Jan 14, 2008 at 07:36 AM
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
Add a comment
-
Posted on Jan 14, 2008 at 07:59 AM
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
Add a comment
-
Former MemberPosted on Jan 14, 2008 at 12:44 PM
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
Add a comment
-
Posted on Jan 17, 2008 at 05:53 AM
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
Add a comment
Add a comment