performance tuning for a select stt on table with 3M records

[former_member339306]

*Original code*

loop at t_likp_lips into wa_likp_lips.

select single netwr into l_netwr from vbrp

where vgbel eq wa_likp_lips-vbeln and vgpos eq wa_likp_lips-posnr.

endloop.

*replaced with*

select vbeln posnr vgbel vgpos netwr

into table t_vbrp from vbrp

for all entries in t_likp_lips

where vgbel eq t_likp_lips-vbeln

and vgpos eq t_likp_lips-posnr.

loop at t_likp_lips into wa_likp_lips.

read table t_vbrp into wa_vbrp with key

vgbel = t_likp_lips-vbeln

vgpos = t_likp_lips-posnr.

endloop.

*t_likp_lips has 7000 entries

*vbrp has 3million records

*even when i changed the code the new select stt is running for hours

*please advise

[FredericGirod]

FOR ALL ENTRIES will consume all your performance

if you have enough memory, the faster is to select ALL the data and made a loop after

if you do this, define the OCCURS statement with the good size (not OCCURS 0)

there is also a method to accelerate the loop

Edited by: Frédéric Girod on May 4, 2010 3:46 PM

[Former Member]

Hello,

Don't forget to SORT your internal table t_vbrp and to use BINARY SEARCH on your READ :

SORT t_vbrp BY vgbel vgpos.

LOOP AT t_likp_lips INTO wa_likp_lips.
  READ TABLE t_vbrp INTO wa_vbrp 
    WITH vgbel = t_likp_lips-vbeln
         vgpos = t_likp_lips-posnr
   BINARY SEARCH.
ENDLOOP.

or better, declare t_vbrp as SORTED TABLE...

Best regards,

Samuel

[ThomasZloch]

Sorry, this is a FAQ in the performance forum, please check the sticky threads and search for keywords like VBRP, VBFA, SD document flow etc.

Thread locked.

Thomas