Performance issue in RSEG

[Former Member]

Hi All,

I got a querry having performance issue.

SELECT belnr

gjahr

buzei

xblnr

lfbnr

FROM rseg APPENDING TABLE l_it_rseg

FOR ALL ENTRIES IN l_it_bkpf

WHERE belnr IN l_r_belnr

AND gjahr = l_it_bkpf-gjahr.

As per me, two of the primary fields are used in selection and should not have much scope for improvemnet.

Still any suggestions so that I can optimise the querry?

[Former Member]

Hi All,

I got a querry having performance issue.

SELECT belnr

gjahr

buzei

xblnr

lfbnr

FROM rseg APPENDING TABLE l_it_rseg

FOR ALL ENTRIES IN l_it_bkpf

WHERE belnr IN l_r_belnr

AND gjahr = l_it_bkpf-gjahr.

As per me, two of the primary fields are used in selection and should not have much scope for improvemnet.

Still any suggestions so that I can optimise the querry?

[ThomasZloch]

Hi,

I don't really understand this part:

FOR ALL ENTRIES IN l_it_bkpf

WHERE belnr IN l_r_belnr

AND gjahr = l_it_bkpf-gjahr.

you're doing a "for all entries" in table l_it_bkpf, but then only use gjahr in the selection, which is not very selective. for selection on belnr you are using l_r_belnr. if this range is emtpy, and rseg has many entries, then your select will run a long time even when using the primary key.

let us know how you fill l_it_bkpf and l_r_belnr, and we might be able to help.

cheers

Thomas

[Former Member]

as Thomas already started, I think your SELECT is logically incorrect!

you take some belnr whci are are in : belnr IN l_r_belnr

and combine them with years which are in here: gjahr = l_it_bkpf-gjahr

that is questionable.

I guess you want to select a list of certain belnr and gjahr combinations!

Siegfried

[Former Member]

I also don't really understand what you mean to do, but you can try this:

DATA: BEGIN OF itab OCCURS 0,
        gjahr TYPE gjahr,
      END OF itab.

LOOP AT l_it_bkpf.
  itab-gjahr = l_it_bkpf-gjahr.
  COLLECT itab.
ENDLOOP.

SELECT belnr gjahr buzei xblnr lfbnr
  FROM rseg APPENDING TABLE l_it_rseg
  FOR ALL ENTRIES IN itab        "<====
  WHERE belnr IN l_r_belnr
    AND gjahr = itab-gjahr.            "<====

This will reduce the niumber of entries in your internal table and should speed it up.

Rob

[Former Member]

Hi all,

Thanks for the quick replies.I need to improve the performance where the basis says might have the problem.Here original developer seems to have gathered all the belnr's in range table and used that with year from l_it_bkpf.Nevertheless l_r_belnr contents are first 10 characters from l_it_bkpf-awkey.So ineffect only one table is used i.e. l_it_bkpf.

Can I change the code to like :

DATA : Begin of l_wa_bkpf_temp occurs 0,

belnr TYPE bkpf-belnr,

gjahr TYPE bkpf-gjahr,

End of l_wa_bkpf.

Loop at l_it_bkpf into l_wa_bkpf.

l_wa_bkpf_temp-belnr = l_wa_bkpf-awkey+0(10).

l_wa_bkpf_temp-gjahr = l_wa_bkpf-gjahr.

Endloop.

Sort l_it_bkpf_temp by belnr gjahr.

Delete adjacent duplicates from l_it_bkpf comparing belnr gjahr.

and then ,

SELECT belnr

gjahr

buzei

xblnr

lfbnr

FROM rseg APPENDING TABLE l_it_rseg

FOR ALL ENTRIES IN l_it_bkpf_temp

WHERE belnr = l_it_bkpf_temp-belnr

AND gjahr = l_it_bkpf_temp-gjahr.

Can this will improve performance significatly?

Or can we improve speed at database level as well? like by creating index..

[ThomasZloch]

this looks pretty good.

skip the "occurs 0" when declaring the workarea l_wa_bkpf_temp.

also, the structure of l_wa_bkpf_temp must be the same as table l_it_bkpf.

better fill l_wa_bkpf_temp-gjahr from l_wa_bkpf-awkey+10(4), that's more consistent.

also check l_it_bkpf_temp is not empty before doing the select on RSEG.

no need for a secondary index since you are using the primary key.

Cheers

Thomas