MSKA query performance

[Former Member]

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

[Former Member]

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

[Caetano]

Hello

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

BR

Caetano

[Former Member]

Hello,

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

[Caetano]

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.

BR

Caetano

[Former Member]

Hello,

You are right, thank you for moving.

Regards.

[jimmy_chan2]

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.

[VenkatRamesh_V]

Hi Gulsah,

Split the Select query into multiple.

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

Hope it helpful.

Regards,

Venkat.

[Former Member]

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

[Clemenss]

Hi Rocky,

can you explain why?

Regards,

Clemens

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Rob,

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

ST05 doesn't show any index.

[Former Member]

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