Performance Issue

[Former Member]

Hi All,

The following Statement takes a lot of time..any suggestions on Improvement.

I cant use FOR ALL ENTRIES since i need the duplicate entries as well...

LOOP AT r_kunnr.

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 kzwi3 kzwi4 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.

ENDLOOP.

[RichHeilman]

I don't have that table in my system, but please make sure that the WHERE clause fields are in the same exact order as the key in the S502 table.

Regards,

Rich Heilman

[Former Member]

DELETE r_kdgrp WHERE NOT low IN s_kdgrp.

DELETE r_bzirk WHERE NOT low IN s_bzirk.

SELECT vbeln vkorg pkunag kdgrp bzirk pkunrg

matnr posnr kzwi3 kzwi4 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

<b>AND pkunag in r_kunnr</b>

AND kdgrp IN r_kdgrp

AND bzirk IN r_bzirk

AND vbeln IN s_vbeln

AND matnr IN s_matnr.

Try the above code.

Regards,

Ravi

[Former Member]

Thanks Rich and Ravi,

I ll try the code and keep you posted.

Thanks

Brain

[Former Member]

Hi

I have tried the code, but it dint improve the performance.

Rich, the order in the where clause is the same as in the table.

Wat else can be done to improve the performance...?

Just to let you all know the number of entries in the table are more than 200 million.

Regards

Brain

[Former Member]

Hi,

Make sure the top portion of the keys are always populated. In your case the fields S_VRSIO, S_VKORG, S_SPBUP can be blank. If it is not populated then populate those ranges.

<b> 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</b>

Otherwise, create an index on the customer field if it always has a value in the select.

Thanks,

Naren

[Former Member]

First, why are you doing the following part of the code in the loop. You can do that outside the loop as these lines don't seem to be dependent on the loop record.

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.

Also, instead of looping at r_kunnr, why can't you use it with IN clause as below

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
       kzwi3 kzwi4  kzwi5 INTO 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
                           <b>AND pkunag IN r_kunnr</b>
                           AND kdgrp  IN r_kdgrp
                           AND bzirk  IN r_bzirk
                           AND vbeln  IN s_vbeln
                           AND matnr  IN s_matnr.

It looks like this is a custom infostructure. If you are having so many millions of records, then you have to make sure that the key fields are passed in the WHERE clause. Do a SQL trace and see if it is doing an index search or sequential search. If any of the fields in the above WHERE clause are non-key fields, then remove them from the WHERE clause and delete the unwanted records after the select. Something like this, assuming VBELN and MATNR are non-key fields.

SELECT vbeln vkorg  pkunag kdgrp
       bzirk pkunrg matnr  posnr
       kzwi3 kzwi4  kzwi5 INTO 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 IN r_kunnr
                           AND kdgrp  IN r_kdgrp
                           AND bzirk  IN r_bzirk.

DELETE t_s502 WHERE NOT ( vbeln  IN s_vbeln AND
                          matnr  IN s_matnr ).