topic Re: performance issue in Application Development and Automation Discussions

performance issue

Former Member — Thu, 31 May 2007 06:02:50 GMT

Hi Experts,

Kindly let me know how can i improve performance of the below query...

SELECT DISTINCT c_{bukrs c}waers c_{prctr b}kostl

a_{kstar a}versn a~gjahr

a_{wkg001 a}wkg002 a_{wkg003 a}wkg004 a_{wkg005 a}wkg006

a_{wkg007 a}wkg008 a_{wkg009 a}wkg010 a_{wkg011 a}wkg012

a_{wkg013 a}wkg014 a_{wkg015 a}wkg016

APPENDING TABLE i_temp

FROM coss AS a

JOIN cssl AS b ON a_{objnr = b}objnr

JOIN csks AS c ON b_{kostl = c}kostl

WHERE ( a~lednr EQ '00'

AND a~versn EQ '000'

AND b~kostl IN r_ccde

AND a~wrttp EQ '04'

AND a~gjahr EQ p_fy

AND a~kstar GE '0000000000'

AND a~kstar LE '0000999999' )

Re: performance issue

Former Member — Thu, 31 May 2007 06:05:09 GMT

Hi Santosh,

Joins reduce the performance of a program.

Better dont use joins and write separate select queries for each table....

In the format...

ex.

SELECT VBELN

AUGRU INTO CORRESPONDING FIELDS OF TABLE IT_VBAK

FROM VBAK

WHERE VBELN IN S_VBELN.

IF SY-SUBRC = 0.

SELECT VBELN

POSNR

MATNR

ZMENG INTO CORRESPONDING FIELDS OF TABLE IT_VBAP

FROM VBAP

FOR ALL ENTRIES IN IT_VBAK

WHERE VBELN = IT_VBAK-VBELN.

IF SY-SUBRC = 0.

SELECT SPRAS

AUGRU

BEZEI INTO CORRESPONDING FIELDS OF TABLE IT_REASON

FROM TVAUT

FOR ALL ENTRIES IN IT_VBAK

WHERE AUGRU = IT_VBAK-AUGRU.

IF SY-SUBRC = 0.

SELECT VBELN

PARVW

KUNNR INTO CORRESPONDING FIELDS OF TABLE IT_VBPA

FROM VBPA

FOR ALL ENTRIES IN IT_VBAK

WHERE VBELN = IT_VBAK-VBELN.

regards,

pritha

Message was edited by:

Pritha Agrawal

Re: performance issue

Former Member — Thu, 31 May 2007 06:09:35 GMT

As you are not joining tables on the basis of keys of those tables, you can use FOR ALL ENTRIES as explained above.

Fetch data from one table first into internal table and then use FOR ALL ENTRIES in <itab> for second SELECT QUERY.

Re: performance issue

S0025444845 — Thu, 31 May 2007 06:18:21 GMT

Hi,

use for all enries instead of join to improve performance.

1) select data from table coss

SELECT objnr kstar versn gjahr

wkg001 wkg002 wkg003 wkg004 wkg005 wkg006

wkg007 wkg008 wkg009 wkg010 wkg011 wkg012

wkg013 wkg014 wkg015 wkg016

into TABLE i_coss

FROM coss

where

lednr EQ '00'

AND versn EQ '000'

AND wrttp EQ '04'

AND gjahr EQ p_fy

AND kstar GE '0000000000'

AND kstar LE '0000999999' ) .

if i_coss is not intial.

select objnr kostl

into i_cssl

from cssl

for all entries in i_coss

where objnr = i_coss-objnr

and kostl IN r_ccde .

endif.

if i_cssl is not initial.

select bukrs waers prctr kostl

into table i_csks

from csks

for all entries in i_cssl

where kostl = it_cssl-kostl.

endif.

regards,

sudha.

reward points igf useful

Re: performance issue

Neslinn — Thu, 31 May 2007 06:28:52 GMT

Remove the inner join and try maintain the views and make the select query from the same..