Performance issue .

[Former Member]

Dear experts,

Is there a way for the following SELECT query to be improved?

The problem that due to this Query....Report is not generated on Production even in bacground for 21 hrs.

How can I improve the data retrieval here?

And I also can't change the SELECT query to be out of the loop ?

LOOP AT it_bkpf INTO wa_bkpf WHERE tcode = 'FB60'.

IF it_bkpf[] IS NOT INITIAL.

SELECT belnr "INVOICE DOC NO.

koart

shkzg

wrbtr "GROSS AMOUNT

wmwst "Tax Amount

lifnr "VENDOR

werks "SITE

FROM bseg

INTO TABLE it_bseg

FOR ALL ENTRIES IN it_bkpf

WHERE belnr = it_bkpf-belnr

AND koart = 'K' .

ENDIF.

IF it_bseg[] IS NOT INITIAL..

SELECT lifnr

name1 "PARTY NAME

FROM lfa1

INTO TABLE it_lfa1

FOR ALL ENTRIES IN it_bseg

WHERE lifnr = it_bseg-lifnr.

ENDIF.

IF it_bkpf[] IS NOT INITIAL.

SELECT belnr

gjahr

buzei

koart

shkzg

wrbtr "AMOUNT

FROM bseg

INTO TABLE it_bseg3

FOR ALL ENTRIES IN it_bkpf

WHERE belnr = it_bkpf-belnr

AND buzei = '2'

AND koart = 'S'.

ENDIF.

IF it_bseg2[] IS NOT INITIAL.

SELECT mwskz

text1 "Text1

FROM t007s

INTO TABLE it_t007s2

FOR ALL ENTRIES IN it_bseg2

WHERE mwskz = it_bseg2-mwskz

AND kalsm = 'TAXINN'

AND spras = 'EN'.

ENDIF.

ENDLOOP.

[Former Member]

there is quite a lot which can be improved, but at first, what i dont really get is why you do your BSEG select in a loop over all the BKPF entries and still in every loop you do a far all entries selection.

I mean iether do that ONCE before the loop or in the loop just select your according BSEG entries for the actual looped BKPF record.

Thos costs you the most time i hardly suspect so we should start there.

Additionally you need to KNOW that BSEG is a really really HUGE table with LOTS of entries.

So you should always aim for a fully qualified select on it, and not just belnr, which is just a 1/4th part of the key.

[Former Member]

Before LOOP statement i am executing this Query :-

SELECT belnr

gjahr "Year

budat "Date

tcode

FROM bkpf

INTO TABLE it_bkpf

WHERE belnr IN s_belnr

AND budat IN s_budat.

[Former Member]

well that is kinda obvious, but still you select on BSEG with for all entries doesnt match.

For every BKPF record you are selecting ALL accodring BSEG records for ALL your BKPF records.

that is too much. i hope you understand the problem now.

[Former Member]

ok...got your point sir.........now....i think.....wud it be better if i select the Required Fields by hitting juz 1 SELECT qry on BSEG and then.....afterwards get all the necessary fields in WA_FINAL by READ statement inside the LOOP at BKPF ???

[Former Member]

Hello Ajay,

I think that first we would need to clarify what you are trying to achieve and why you have this code within the loop. Without this full understanding it is difficult to give you the best solution. Another point is that the "FOR ALL ENTRIES" is highly inefficient when there are many entries in the referenced table. This is due to SAP converting the records in the referenced table into additional selection criteria in the SQL call on the database. Usually a new SQL call is made for every 20 to 50 records in the reference table.

My suggestion would be the following.

select all entries in LFA1 and T007S into sorted internal tables with keys to match the fields in the where conditions in your select statements. You can then get the information from these tables using a READ TABLE statement which doesn't need a further SQL call. This is done before you start on the BKPF and BSEG selections.

Use a join on BKPF and BSEG for your selections. This will then result in single select queries for each of the internal tables linking to either LFA1 or T007S.

You then loop through the BKPF/BSEG tables selecting the LFA1 or T007S records for each line.

Regards,

John.

[Former Member]

Moderator message - Please see before posting - post locked Also have a look at the performance examples in SE38. This has a number of performance problems. You should take care of what you can before asking for help. Rob