12-30-2014 10:10 AM
Hi,
How to optimize this query ? When i monitored my programme from ST05,most of time is spending in this query.
SELECT c~vbeln c~auart c~kunnr d~name1 e~kdmat e~posnr e~arktx
e~abgru
b~mtart b~zz_ugerw AS urungr
a~matnr f~maktx a~lgort a~charg a~kalab a~kains
a~kaspe b~meins a~werks b~matkl h~bwtar i~strgr e~cuobj
c~vkorg c~vtweg
e~matnr AS sipmat
INTO CORRESPONDING FIELDS OF TABLE tb_mska
FROM mska AS a
INNER JOIN mara AS b ON b~matnr = a~matnr
INNER JOIN vbak AS c ON c~vbeln = a~vbeln
INNER JOIN marc AS i ON i~matnr = a~matnr
AND i~werks = a~werks
LEFT JOIN kna1 AS d ON d~kunnr = c~kunnr
INNER JOIN vbap AS e ON e~vbeln = a~vbeln
AND e~posnr = a~posnr
LEFT JOIN makt AS f ON a~matnr = f~matnr
AND f~spras = sy-langu
LEFT JOIN mcha AS h ON a~matnr = h~matnr
AND a~werks = h~werks
AND a~charg = h~charg
WHERE a~matnr IN so_matnr
AND a~werks IN so_werks
AND a~lgort IN so_lgort
AND a~charg IN so_charg
AND a~vbeln IN so_vbeln
AND e~posnr IN so_posnr
AND b~mtart IN so_mtart
AND c~kunnr IN so_kunnr
AND c~auart IN so_auart
AND c~abrvw IN so_abrvw
AND e~matnr IN so_sipma
AND ( a~kalab NE 0
OR a~kains NE 0
OR a~kaspe NE 0 ) .
12-31-2014 1:53 AM
Hi,
Don't use the left join command
SELECT c~vbeln c~auart d~name1 e~kdmat e~posnr e~arktx
e~abgru
b~mtart b~zz_ugerw AS urungr
a~matnr a~lgort a~charg a~kalab a~kains
a~kaspe b~meins a~werks b~matkl i~strgr e~cuobj
c~vkorg c~vtweg
e~matnr AS sipmat
INTO CORRESPONDING FIELDS OF TABLE tb_mska
FROM mska AS a
INNER JOIN mara AS b ON b~matnr = a~matnr
INNER JOIN vbak AS c ON c~vbeln = a~vbeln
INNER JOIN marc AS i ON i~matnr = a~matnr
AND i~werks = a~werks
INNER JOIN vbap AS e ON e~vbeln = a~vbeln
AND e~posnr = a~posnr.
WHERE a~matnr IN so_matnr
AND a~werks IN so_werks
AND a~lgort IN so_lgort
AND a~charg IN so_charg
AND a~vbeln IN so_vbeln
AND e~posnr IN so_posnr
AND b~mtart IN so_mtart
AND c~kunnr IN so_kunnr
AND c~auart IN so_auart
AND c~abrvw IN so_abrvw
AND e~matnr IN so_sipma
AND ( a~kalab NE 0
OR a~kains NE 0
OR a~kaspe NE 0 ) .
LOOP AT tb_mska.
select single name1 into tb_mska-name1 from kna1 where kna1 = tb_mska-kunnr.
select single maktx into tb_mska-maktx from makt where matnr = tb_mska-matnr and spras = sy-langu.
select single bwtar into tb_mska-bwtar from mcha where matnr = tb_mska-matnr and werks = tb_mska-werks and charg = tb_mska-charg.
MODIFY tb_mska.
ENDLOOP.
12-30-2014 6:20 PM
Hello
Is this query part of a standard SAP program or transaction? If so, can you please specify which program or transaction?
BR
Caetano
12-30-2014 6:55 PM
Hello,
It is used in a z programme. it is not a standart programme query.
12-30-2014 10:10 PM
12-31-2014 7:33 AM
12-31-2014 1:53 AM
Hi,
Don't use the left join command
SELECT c~vbeln c~auart d~name1 e~kdmat e~posnr e~arktx
e~abgru
b~mtart b~zz_ugerw AS urungr
a~matnr a~lgort a~charg a~kalab a~kains
a~kaspe b~meins a~werks b~matkl i~strgr e~cuobj
c~vkorg c~vtweg
e~matnr AS sipmat
INTO CORRESPONDING FIELDS OF TABLE tb_mska
FROM mska AS a
INNER JOIN mara AS b ON b~matnr = a~matnr
INNER JOIN vbak AS c ON c~vbeln = a~vbeln
INNER JOIN marc AS i ON i~matnr = a~matnr
AND i~werks = a~werks
INNER JOIN vbap AS e ON e~vbeln = a~vbeln
AND e~posnr = a~posnr.
WHERE a~matnr IN so_matnr
AND a~werks IN so_werks
AND a~lgort IN so_lgort
AND a~charg IN so_charg
AND a~vbeln IN so_vbeln
AND e~posnr IN so_posnr
AND b~mtart IN so_mtart
AND c~kunnr IN so_kunnr
AND c~auart IN so_auart
AND c~abrvw IN so_abrvw
AND e~matnr IN so_sipma
AND ( a~kalab NE 0
OR a~kains NE 0
OR a~kaspe NE 0 ) .
LOOP AT tb_mska.
select single name1 into tb_mska-name1 from kna1 where kna1 = tb_mska-kunnr.
select single maktx into tb_mska-maktx from makt where matnr = tb_mska-matnr and spras = sy-langu.
select single bwtar into tb_mska-bwtar from mcha where matnr = tb_mska-matnr and werks = tb_mska-werks and charg = tb_mska-charg.
MODIFY tb_mska.
ENDLOOP.
12-31-2014 2:33 AM
Hi Gulsah,
Split the Select query into multiple.
don't use condition statement for a non-key fields.
Hope it helpful.
Regards,
Venkat.
12-31-2014 4:43 AM
Hi Gulsah ,
where u r joining more than 3 tables , it is better to use for all entries , this gives better performance than inner joins .
Regards ,
rocky
01-03-2015 12:09 AM
12-31-2014 5:17 AM
Hi,
To improvise the performance and for the better understanding of the logic, it will be advisable to split the query wherein each query is using JOIN on two tables or maximum three. Also left join hampers the performance. If possible we can get rid of the left join with inner join and use FOR ALL ENTRIES keeping in mind below points on the table on which for all entries is put:
1. check table is not initial.
2. sort the table on the fields on the reference of which the selection is to be made.
3. delete adjacent duplicate comparing the fields on which table is SORT.
Regards,
Aashika
12-31-2014 5:24 AM
Hi
Just split the query according to mandatory fields.
It is advisable not to JOIN more than 2 tables. Just go for FOR ALL ENTRIES.
regards
laxman
12-31-2014 5:59 AM
Hi Kaya,
While writing the select query majorly we have to follow the few points
1) Build WHERE clause on PRIMARY KEYS / Secondary Keys,
2) Avoid to use the MOVE-CORRESPONDING Statement instead Declare the internal table with the required fields.
3) Instead writing INNER JOINS use the FOR ALL ENTRIES(my Suggestion)
4) SORT the internal table based on required selection fields
5) follow the same sequential order in DDIC table in Select statement.
12-31-2014 4:40 PM
l krishna bodduluri wrote:
Hi Kaya,
While writing the select query majorly we have to follow the few points
1) Build WHERE clause on PRIMARY KEYS / Secondary Keys,
2) Avoid to use the MOVE-CORRESPONDING Statement instead Declare the internal table with the required fields. Wrong
3) Instead writing INNER JOINS use the FOR ALL ENTRIES(my Suggestion) Wrong
4) SORT the internal table based on required selection fields
5) follow the same sequential order in DDIC table in Select statement. Wrong
12-31-2014 4:36 PM
The problem is likely do mainly from all of your NE conditions joined by OR. Also look at the contents of the range tables. Does ST05 show any index being used or is it a full table scan?
You haven't given enough information to give any help.
Rob
01-02-2015 11:59 AM
Hi Rob,
How can i join NE conditions not using OR statement. ?
ST05 doesn't show any index.
01-02-2015 6:23 PM
Determine from the domain what values are allowed and use them instead (but exclude the 0).
An index stops working when it encounters NE.
Rob