05-31-2007 7:02 AM
Hi Experts,
Kindly let me know how can i improve performance of the below query...
SELECT DISTINCT cbukrs cwaers cprctr bkostl
akstar aversn a~gjahr
awkg001 awkg002 awkg003 awkg004 awkg005 awkg006
awkg007 awkg008 awkg009 awkg010 awkg011 awkg012
awkg013 awkg014 awkg015 awkg016
APPENDING TABLE i_temp
FROM coss AS a
JOIN cssl AS b ON aobjnr = bobjnr
JOIN csks AS c ON bkostl = ckostl
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' )
05-31-2007 7:05 AM
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
05-31-2007 7:05 AM
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
05-31-2007 7:09 AM
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.
05-31-2007 7:18 AM
Hi,
use for all enries instead of join to improve performance.
1) select data from table coss
like
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
05-31-2007 7:28 AM
Remove the inner join and try maintain the views and make the select query from the same..