Skip to Content
avatar image
Former Member

MSKA query performance

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    Dec 31, 2014 at 01: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.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 31, 2014 at 02: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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2014 at 04: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2014 at 05: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2014 at 05: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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 31, 2014 at 05: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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Dec 30, 2014 at 06: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2014 at 04: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

    Add comment
    10|10000 characters needed characters exceeded