Slow Report

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

Thank you Rob. This works well.

[Former Member]

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