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

  • Follow
  • 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