Help with Performance.

[Former Member]

Hi All,

The code below takes long time to run.can anyone suggest some ideas to fine tune the program.

r_kunnr-option = 'EQ'.

r_kunnr-sign = 'I'.

CLEAR: r_kunnr-high, r_kunnr-low.

SELECT kunnr

INTO knvp-kunnr

FROM knvp

WHERE parvw EQ c_parvw

AND kunn2 IN s_kunnr.

r_kunnr-low = knvp-kunnr.

COLLECT r_kunnr.

CLEAR r_kunnr-low.

ENDSELECT.

LOOP AT r_kunnr.

REFRESH r_kdgrp.

REFRESH r_bzirk.

r_kdgrp-option = 'EQ'.

r_kdgrp-sign = 'I'.

r_bzirk-option = 'EQ'.

r_bzirk-sign = 'I'.

SELECT kdgrp bzirk INTO (knvv-kdgrp, knvv-bzirk)

FROM knvv

WHERE kunnr = r_kunnr-low.

r_kdgrp-low = knvv-kdgrp.

COLLECT r_kdgrp.

CLEAR r_kdgrp-low.

r_bzirk-low = knvv-bzirk.

COLLECT r_bzirk.

CLEAR r_bzirk-low.

ENDSELECT.

REFRESH t_cdhdr.

SELECT objectid changenr username udate utime tcode

FROM cdhdr INTO TABLE t_cdhdr

WHERE objectclas EQ 'DEBI'

AND objectid EQ r_kunnr-low

AND udate IN r_udate.

IF t_cdhdr[] IS NOT INITIAL.

SELECT fname value_new value_old

INTO (cdpos-fname, cdpos-value_new, cdpos-value_old)

FROM cdpos

FOR ALL ENTRIES IN t_cdhdr

WHERE objectclas EQ 'DEBI'

AND objectid EQ t_cdhdr-objectid

AND changenr EQ t_cdhdr-changenr

AND tabname EQ 'KNVV'.

IF cdpos-fname = 'KDGRP'.

MOVE cdpos-value_new TO r_kdgrp-low.

COLLECT r_kdgrp.

MOVE cdpos-value_old TO r_kdgrp-low.

COLLECT r_kdgrp.

ENDIF.

IF cdpos-fname = 'BZIRK'.

MOVE cdpos-value_new TO r_bzirk-low.

COLLECT r_bzirk.

MOVE cdpos-value_old TO r_bzirk-low.

COLLECT r_bzirk.

ENDIF.

ENDSELECT.

ENDIF.

DELETE r_kdgrp WHERE NOT low IN s_kdgrp.

DELETE r_bzirk WHERE NOT low IN s_bzirk.

CHECK r_kdgrp[] IS NOT INITIAL.

CHECK r_bzirk[] IS NOT INITIAL.

SELECT vbeln vkorg pkunag kdgrp bzirk pkunrg

matnr posnr SUM( kzwi3 ) SUM( kzwi4 ) SUM( kzwi5 )

APPENDING TABLE t_s502

FROM s502

WHERE ssour EQ space

AND vrsio IN s_vrsio

AND spmon EQ '000000'

AND sptag EQ '00000000'

AND spwoc EQ '000000'

AND vkorg IN s_vkorg

AND spbup IN s_spbup

AND pkunag EQ r_kunnr-low

AND kdgrp IN r_kdgrp

AND bzirk IN r_bzirk

AND vbeln IN s_vbeln

AND matnr IN s_matnr

GROUP BY vbeln vkorg pkunag kdgrp bzirk pkunrg matnr posnr.

ENDLOOP.

CHECK NOT t_s502[] IS INITIAL.

SORT t_s502 BY vbeln.

Any help is appreciated.

Thanks

Brain.

[RichHeilman]

First, get rid of the SELECT....ENDSELECTS. Use SELECT...INTO TABLE.

REgards,

Rich Heilman

[former_member186741]

1. IT MIGHT BE GOOD TO HAVE A CDHDR index on objectclass objectid and udate

2. change cdpos retrieval slightly

DATA T_CDPOS TYPE TABLE OF CDPOS.

SELECT fname value_new value_old

INTO CORRESPONDING FIELDS OF TABLE T_cdpos

FROM cdpos

FOR ALL ENTRIES IN t_cdhdr

WHERE objectclas EQ 'DEBI'

AND objectid EQ t_cdhdr-objectid

AND changenr EQ t_cdhdr-changenr

AND tabname EQ 'KNVV'

AND fname IN ('KDGRP','BZIRK').

.

LOOP AT T_CDPOS INTO CDOS.

IF cdpos-fname = 'KDGRP'.

MOVE cdpos-value_new TO r_kdgrp-low.

COLLECT r_kdgrp.

MOVE cdpos-value_old TO r_kdgrp-low.

COLLECT r_kdgrp.

ENDIF.

IF cdpos-fname = 'BZIRK'.

MOVE cdpos-value_new TO r_bzirk-low.

COLLECT r_bzirk.

MOVE cdpos-value_old TO r_bzirk-low.

COLLECT r_bzirk.

ENDIF.

ENDLOOP.

3. the S502 work is probably best done without the aggregate function 'sum' as this can be a killer. Change this select to stop using 'sum' and then loop and collect the values into another table after the sql.

[Former Member]

Hi,

1. First fetch the data whatever required into internal tables and then processing them.

2. Try to avoid the SELECT statements in the loop.

3. Instead of using select..endselect use select ...into table.

Regards,

Azaz Ali.