Slow Report

Former Member — Mon, 12 Dec 2005 16:12:47 GMT

I have inherited a program that produces a list in such a manner that an itab with usually about 150,000 records has a nested loop within another itab usually containing about the same number of recs. I have successfully lessened the processing time for some other programs doing similar processing, but this one isn't quite the same. Any suggestions on how to speed up the following code. Your comments are greatly appreciated, as the report is taking many hours to run because of this part of the code.

Kind Regards,

Jason

LOOP AT i_state INTO wa_i_state.

IF wa_i_state-rfarea = '1'.

LOOP AT i_pl_lob_rec INTO wa_i_pl_lob_rec
WHERE bukrs = wa_i_state-bukrs
AND racct = wa_i_state-racct
AND rprctr = wa_i_state-rprctr
AND zzreint = wa_i_state-zzreint
AND rfarea = wa_i_state-rfarea
AND rzzregio = wa_i_state-rzzregio.

lob_total = lob_total + wa_i_pl_lob_rec-hsl.
ENDLOOP.

ELSE.

LOOP AT i_wp_lob_rec INTO wa_i_wp_lob_rec
WHERE bukrs = wa_i_state-bukrs
AND racct = wa_i_state-racct
AND rprctr = wa_i_state-rprctr
AND zzreint = wa_i_state-zzreint
AND rfarea = wa_i_state-rfarea
AND rzzregio = wa_i_state-rzzregio.

lob_total = lob_total + wa_i_wp_lob_rec-hsl.
ENDLOOP.
ENDIF.

difference = wa_i_state-hsl - lob_total.

WRITE AT /2 wa_i_state-bukrs.
WRITE AT 9 wa_i_state-racct.
WRITE AT 21 wa_i_state-rprctr.
WRITE AT 34 wa_i_state-zzreint.
WRITE AT 42 wa_i_state-rfarea.
WRITE AT 49 wa_i_state-rzzregio.
WRITE AT 55 wa_i_state-rzzlob.
WRITE AT 68(20) wa_i_state-hsl.
WRITE AT 90(20) lob_total.
WRITE AT 112(20) difference.

CLEAR: lob_total.
ENDLOOP.

The following is how I sped up processing time with another program, but it didn't contain a conditional like the above code.

LOOP AT i_lob INTO wa_i_lob WHERE rfarea = '1'.

AT END OF racct.

SUM.

wa_i_report_rec-bukrs = wa_i_lob-bukrs.

wa_i_report_rec-racct = wa_i_lob-racct.

wa_i_report_rec-offsethsl = wa_i_lob-hsl.

APPEND wa_i_report_rec TO i_report_rec.

ENDAT.

ENDLOOP.

LOOP AT i_allocs INTO wa_i_allocs.

CLEAR: wa_i_allocs-docty, wa_i_allocs-docnr, wa_i_allocs-docln,

wa_i_allocs-sgtxt, wa_i_allocs-budat, wa_i_allocs-poper,

wa_i_allocs-ryear, wa_i_allocs-glx_budat, wa_i_allocs-bukrs,

wa_i_allocs-usnam, wa_i_allocs-rtcur, wa_i_allocs-lcurr.

wa_i_report_rec-aemyhsl = wa_i_report_rec-aemyhsl +

wa_i_allocs-hsl.

AT END OF racct.

wa_i_report_rec-racct = wa_i_allocs-racct.

MULTIPLY wa_i_report_rec-aemyhsl BY -1.

MODIFY TABLE i_report_rec FROM wa_i_report_rec

TRANSPORTING aemyhsl.

CLEAR wa_i_report_rec-aemyhsl.

ENDAT.

ENDLOOP.

LOOP AT i_report_rec INTO wa_i_report_rec.

wa_i_report_rec-diffhsl = wa_i_report_rec-offsethsl +

wa_i_report_rec-aemyhsl.

MODIFY TABLE i_report_rec FROM wa_i_report_rec

TRANSPORTING diffhsl.

ENDLOOP.

IF NOT i_report_rec IS INITIAL.

CREATE OBJECT offset_grid

EXPORTING i_parent = offset_container.

SET SCREEN 100.

ENDIF.

Re: Slow Report

Former Member — Mon, 12 Dec 2005 16:25:06 GMT

Replace the nested loops with binary reads (to get the first matching record) add 1 to the index of that record to get the next matching record. Keep adding 1 and doing indexed reads until either the key changes or you run out of records. This is the most efficient way to get rid of nested loops.

Rob

Re: Slow Report

Former Member — Mon, 12 Dec 2005 16:29:24 GMT

Earlier, I posted an of how to do this and the performance boost you can get.

Rob

Re: Slow Report

Former Member — Mon, 12 Dec 2005 19:20:09 GMT

Thank you Rob. This works well.

Re: Slow Report

Former Member — Mon, 12 Dec 2005 19:35:33 GMT

Glad to help.

If you ask most developers how to tune a report, they'll concentrate on the SELECT statements, but in my experience, nested loops over large tables can be far worse.

Rob

topic Re: Slow Report in Application Development and Automation Discussions

Slow Report

Re: Slow Report

Re: Slow Report

Re: Slow Report

Re: Slow Report