topic Re: MSKA query performance in Application Development and Automation Discussions

MSKA query performance

Former Member — Tue, 30 Dec 2014 10:10:51 GMT

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 ) .

Re: MSKA query performance

Caetano — Tue, 30 Dec 2014 18:20:00 GMT

Hello

Is this query part of a standard SAP program or transaction? If so, can you please specify which program or transaction?

Caetano

Re: MSKA query performance

Former Member — Tue, 30 Dec 2014 18:55:59 GMT

Hello,

It is used in a z programme. it is not a standart programme query.

Re: MSKA query performance

Caetano — Tue, 30 Dec 2014 22:10:27 GMT

Hello

If this is a Z program, why did you open this thread to the space ?

As far as I could observe there is no PP table involved in this issue and since it happens on a custom program I'm moving to the ABAP space.

Caetano

Re: MSKA query performance

jimmy_chan2 — Wed, 31 Dec 2014 01:53:10 GMT

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.

Re: MSKA query performance

VenkatRamesh_V — Wed, 31 Dec 2014 02:33:20 GMT

Hi Gulsah,

Split the Select query into multiple.

don't use condition statement for a non-key fields.

Hope it helpful.

Regards,

Venkat.

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 04:43:17 GMT

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

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 05:17:12 GMT

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

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 05:24:50 GMT

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

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 05:59:37 GMT

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.

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 07:33:23 GMT

Hello,

You are right, thank you for moving.

Regards.

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 16:36:10 GMT

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

Re: MSKA query performance

Former Member — Wed, 31 Dec 2014 16:40:12 GMT


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

Re: MSKA query performance

Former Member — Fri, 02 Jan 2015 11:59:48 GMT

Hi Rob,

How can i join NE conditions not using OR statement. ?

ST05 doesn't show any index.

Re: MSKA query performance

Former Member — Fri, 02 Jan 2015 18:23:54 GMT

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

Re: MSKA query performance

Clemenss — Sat, 03 Jan 2015 00:09:31 GMT

Hi Rocky,

can you explain why?

Regards,

Clemens