topic Performance optimization in Select query in Application Development and Automation Discussions

Performance optimization in Select query

Former Member — Fri, 02 Mar 2007 06:48:17 GMT

Hi All,

Please suggest good performance practice for the below code.

SELECT * FROM BKPF WHERE BUKRS IN SO_BUKRS

AND BELNR IN SO_BELNR

AND GJAHR IN SO_GJAHR

AND BLART IN SO_BLART

AND BUDAT IN SO_BUDAT

AND USNAM IN SO_USNAM.

CLEAR BSEG.

SELECT * FROM BSEG WHERE BUKRS EQ BKPF-BUKRS

AND BELNR EQ BKPF-BELNR

AND GJAHR EQ BKPF-GJAHR

AND KOART EQ 'K'.

SELECT SINGLE * FROM LFA1 WHERE LIFNR EQ BSEG-LIFNR

AND KTOKK IN SO_KTOKK.

CHECK SY-SUBRC EQ 0.

MOVE-CORRESPONDING BKPF TO ITAB2.

MOVE BSEG-LIFNR TO ITAB2-LIFNR.

APPEND ITAB2.

ENDSELECT.

PERFORM CHECK_FOR_REVERSAL.

ENDSELECT.

Thanks

Sonal

Re: Performance optimization in Select query

Former Member — Fri, 02 Mar 2007 06:53:02 GMT

Hi,

Don't use Select * in the querys.

Declare 3 internal tables for each table fields, BKPF, BSEG and LFA1.

First fetch the respective fields from BKPF into ttable ITAB,

Then for all entries of ITAB fetch the required fields from BESG.

Then for all entries(Vendors) of BSEG fetch the LFA1 records.

The LOOP at ITAB.

Read the ITAB1 and ITAB2 and append into a final internal table.

Regards,

anji

Re: Performance optimization in Select query

Former Member — Fri, 02 Mar 2007 06:54:56 GMT

Hi,

First select data from BKPF to itab.

and use 'For all entries in'

Never use end select. that too ur using BSEG tables.

so avoid endselect.

regards

satish

Re: Performance optimization in Select query

Former Member — Fri, 02 Mar 2007 06:56:33 GMT

declare internal table for BKPF, BSEG & LFA1 separately.

instead of using Select & End select, use for all entries.

SELECT * FROM BKPF into table itab1 WHERE BUKRS IN SO_BUKRS

AND BELNR IN SO_BELNR

AND GJAHR IN SO_GJAHR

AND BLART IN SO_BLART

AND BUDAT IN SO_BUDAT

AND USNAM IN SO_USNAM.

if not itab1[] is initial.

sort bkpf by belnr.

SELECT * FROM BSEG into table itab2

for all entries in itab1

WHERE BUKRS EQ itab1-BUKRS

AND BELNR EQ itab1-BELNR

AND GJAHR EQ itab1-GJAHR

AND KOART EQ 'K'.

if not itab2[] is initial.

sort itab2 by lifnr

SELECT SINGLE * FROM LFA1 into table itab3

for all entries in itab2

WHERE LIFNR EQ itab2-LIFNR

AND KTOKK IN SO_KTOKK.

endif.

sort itab1 by belnr.

sort itab2 by belnr.

sort itab3 by lifnr.

loop at itab1.

move-corresponding itab1 to itfinal.

loop at itab2 where belnr = itab1-belnr.

move-corresponding itab2 to itfinal.

read itab3 with key lifnr = itab2-lifnr.

if sy-subrc = 0.

move-corresponding itab3 to itfinal.

endif.

endloop.

append itfinal.

clear itfinal.

endloop.

Re: Performance optimization in Select query

Former Member — Fri, 02 Mar 2007 07:07:04 GMT

Hi Sonal,

1)Please create internal tables for BKPF only with required fields, do the same for BSEG and LFA1.

2) After Selecting values from BKPF into t_BKPF.

if t_BKPF[] is not initial

Select using for all entries from BSEG into t_bseg..

Endif.

3) if t_bseg[] is not initail

Select single * from LFA1 .

4)Use Move corresponding to mobe to ITAB2.

4)Loop at itab2 into w_itab2..

MOVE BSEG-LIFNR TO ITAB2-LIFNR.

append itab2 .

endloop.

reward Points if useful

regards

Avi....