Performance Tuning (about mseg/mkpf)

[Former Member]

Hi,experts

I have a performance tuning issue from aging report(zprogram):

I found the cause of  performance degragation is fetch MSEG/MKPF,so I copy original version to other program and change the original program.

Then I create a view for mseg join to mkpf and use "select zview...for all entries in itab"  instead of  "loop at itab : select innerjoin mkpf/mseg....."

after executing and comparing ,

I noticed that the performance of new version is very better than old version if data volume is not very large,  

but if data volume is very large then the old version is less better than new version...

I want to konw why and how to totally increase the performance ?

The following is comparison of old and new version:

old version :

   LOOP AT ITAB_MARD.
      PERFORM NOTMAL_MVT USING ITAB_MARD-MATNR ITAB_MARD-WERKS ITAB_MARD-LGORT.
  ENDLOOP.

   FORM NOTMAL_MVT USING P_MATNR P_WERKS P_LGORT.
*** Get Mvt.Type 101  ***
  SELECT  T1~MATNR T1~WERKS T1~LGORT T1~MBLNR T1~ZEILE T1~LIFNR
          T1~EBELN T1~EBELP T1~MENGE T1~DMBTR T2~BUDAT
    APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
         PACKAGE SIZE 10000
    FROM  MSEG AS T1
           INNER JOIN MKPF AS T2
            ON T1~MBLNR = T2~MBLNR
            AND T1~MJAHR = T2~MJAHR
    WHERE MATNR = ITAB_MARD-MATNR                 " Material
                  AND T1~WERKS = ITAB_MARD-WERKS  " Plant code
                  AND T1~LGORT = ITAB_MARD-LGORT  " location
                  AND ( T1~BWART = '101' or   T1~BWART = '861')          " '101' PO-GR
                  AND T1~SOBKZ <> 'K'.
  ENDSELECT.

*** Get Mvt.Type 131 / 411  ***
  SELECT  T1~MATNR T1~WERKS T1~LGORT T1~MBLNR T1~ZEILE T1~LIFNR
          T1~EBELN T1~EBELP T1~MENGE T1~DMBTR T2~BUDAT
    APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
         PACKAGE SIZE 10000
    FROM  MSEG AS T1
           INNER JOIN MKPF AS T2
            ON T1~MBLNR = T2~MBLNR
            AND T1~MJAHR = T2~MJAHR
    WHERE MATNR = ITAB_MARD-MATNR  " Material
                  AND T1~WERKS = ITAB_MARD-WERKS  " Plant code
                  AND T1~LGORT = ITAB_MARD-LGORT  " location
                  AND ( T1~BWART = '131' OR T1~BWART = '411' )
                  AND ( T1~SOBKZ = ' '   OR       "//Special stock indicator
                       T1~SOBKZ = 'E' )
                  AND T1~LGORT NE SPACE.
  ENDSELECT.

*** Get Mvt.Type 413  ***
  SELECT  T1~MATNR T1~WERKS T1~LGORT T1~MBLNR T1~ZEILE T1~LIFNR
          T1~EBELN T1~EBELP T1~MENGE T1~DMBTR T2~BUDAT
    APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
         PACKAGE SIZE 10000
    FROM  MSEG AS T1
           INNER JOIN MKPF AS T2
            ON T1~MBLNR = T2~MBLNR
            AND T1~MJAHR = T2~MJAHR
    WHERE MATNR = ITAB_MARD-MATNR  " Material
                  AND T1~WERKS = ITAB_MARD-WERKS  " Plant code
                  AND T1~LGORT = ITAB_MARD-LGORT  " location
                  AND ( T1~BWART = '413' AND T1~SOBKZ = 'E' )
                  AND T1~LGORT NE SPACE.
  ENDSELECT.

*** Get Mvt.Type 531  ***
  SELECT  T1~MATNR T1~WERKS T1~LGORT T1~MBLNR T1~ZEILE T1~LIFNR
          T1~EBELN T1~EBELP T1~MENGE T1~DMBTR T2~BUDAT
    APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
         PACKAGE SIZE 10000
    FROM  MSEG AS T1
           INNER JOIN MKPF AS T2
            ON T1~MBLNR = T2~MBLNR
            AND T1~MJAHR = T2~MJAHR
    WHERE MATNR = ITAB_MARD-MATNR  " Material
                  AND T1~WERKS = ITAB_MARD-WERKS  " Plant code
                  AND T1~LGORT = ITAB_MARD-LGORT  " location
                  AND T1~BWART = '531'
                  AND T1~LGORT NE SPACE.
  ENDSELECT.

*** Get Mvt.Type 561/ 653/ 907/ 953  ***
  SELECT  T1~MATNR T1~WERKS T1~LGORT T1~MBLNR T1~ZEILE T1~LIFNR
          T1~EBELN T1~EBELP T1~MENGE T1~DMBTR T2~BUDAT
    APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
         PACKAGE SIZE 10000
    FROM  MSEG AS T1
           INNER JOIN MKPF AS T2
            ON T1~MBLNR = T2~MBLNR
            AND T1~MJAHR = T2~MJAHR
    WHERE MATNR = ITAB_MARD-MATNR  " Material
                  AND T1~WERKS = ITAB_MARD-WERKS  " Plant code
                  AND T1~LGORT = ITAB_MARD-LGORT  " location
*                  AND ( T1~BWART = '561' OR T1~BWART = '653'
                  AND ( T1~BWART = '653'
                  OR T1~BWART = '907' OR T1~BWART = '953' )
                  AND T1~SOBKZ <> 'K' AND T1~LGORT NE SPACE.
  ENDSELECT.
ENDFORM.

new version:

   FORM NOTMAL_MVT_Z1.

SELECT MATNR WERKS LGORT MBLNR ZEILE LIFNR
       EBELN EBELP MENGE DMBTR BUDAT
  APPENDING CORRESPONDING FIELDS OF TABLE ITAB_101
  PACKAGE SIZE 10000     

  FROM YMSEGV_1
    FOR ALL ENTRIES IN ITAB_MARD
  WHERE MATNR = ITAB_MARD-MATNR
    AND WERKS = ITAB_MARD-WERKS
    AND LGORT = ITAB_MARD-LGORT
    AND ( ( ( BWART = '101' or BWART = '861')  AND SOBKZ <> 'K' )
       or ( ( BWART = '131' or BWART = '411')  AND ( SOBKZ <> 'K' or SOBKZ = 'E' ) )
       or ( BWART = '413' AND SOBKZ = 'E' )
       or ( bwart = '531' )
       or ( ( BWART = '653' or BWART = '907' or BWART = '953') AND SOBKZ <> 'K' ) ) .
ENDSELECT.

[Former Member]

Dear Rayden,

First of all, that was good job to reduce the number of select statements to improve the performance. Further aspects that you can consider are following:

• Instead of appending corresponding fields of internal table, create a internal table with required fields and then loop it and move the fields to the required table, that way you move the action from database server to the application server.
• Put a check to make sure that the internal table ITAB_MARD is not empty, as if the internal table is empty for all entries will fetch the entire table YMSEGV_1.
• As the table YMSEGV_1 is custom table, make the fields you are querying as primary fields if noy possible create index on the fields that you are querying.
• Avoid <> conditions in Where statement as it impacts the performance hugely. Rather get the entries for the movement type and then delete the unwanted entries from the internal table.

Based on your code, these are the changes I can suggest would make some difference.

Best Regards,

Imran

[Former Member]

Dear Imran :

Thank you for your reply,

I have followed your suggestion and  try to modify the code,

but after tracing , I noticed the program will stop for a long time when run "for all entries in"  ...

This situation is caused by what? the "for all entries in" table is too large?

or the select datas are too lagre ?

[Former Member]

Dear Rayden,

How big is your internal table that you are using in for all entries? Can you share the code snippet, so that we can quickly check for what might be the reason.

[Former Member]

Hi Rayden,

I pretty much thought about the same points that Imran had mentioned.

I think the join on MSEG and MKPF itself can be avoided. I see that the MKPF table is used in the join only to select BUDAT field but the posting date is available in MSEG also, please check for MSEG-BUDAT_MKPF. If this works for you the same way, then you don't need to create a view or use a view/join in your program. I hope that should significantly improve the performance.

I had the same point on movement type (BWART) and Special Stock Indicator (SOBKZ), it could be removed from the where clause as it complicates the query with so many OR conditions. You could get all the data without checking for BWART and SOBKZ, and ignore the unwanted data by checking for the various movement types with in a loop.

For the exact query which you had reg the for all entries or large table volume. Please post the number of records after the cursor select in MARD fetch and MSEG fetch.

With regards,

Bhaskar R

[Former Member]

Dear Irman :

Here is data volume of internal table ITAB_MARD :

the codes of "select ... for all entries in" as follows: (YMSEGV_1 is the view of mseg join mkpf)

   SELECT MATNR WERKS LIFNR LGORT BUDAT_MKPF MBLNR ZEILE
       EBELN EBELP MENGE DMBTR BWART SOBKZ
  APPENDING TABLE ITAB_101x
  PACKAGE SIZE 10000
FROM YMSEGV_1
    FOR ALL ENTRIES IN ITAB_MARD

   WHERE MATNR = ITAB_MARD3X-MATNR
    AND WERKS = ITAB_MARD3X-WERKS
    AND LGORT = ITAB_MARD3X-LGORT
    AND BWART IN ('101','861','131','411','413','531','653','907','953').

    ENDSELECT.

    LOOP AT ITAB_101X WHERE
        ( ( ( BWART = '101' or BWART = '861')  AND SOBKZ NE 'K' )
       or ( ( BWART = '131' or BWART = '411')  AND ( SOBKZ NE 'K' or SOBKZ = 'E' ) )
       or ( BWART = '413' AND SOBKZ = 'E' )
       or ( bwart = '531' )
       or ( ( BWART = '653' or BWART = '907' or BWART = '953') AND SOBKZ NE 'K' ) ) .
    APPEND ITAB_101X TO ITAB_101.
    ENDLOOP.
    CLEAR ITAB_101X[].

[Former Member]

Dear Bhaskar :

Thank you for your reply,

I found MSEG-BUDAT_MKPF, but it's blank...all mseg records are blank,

is there any img config can set it?

[Former Member]

Copied from another forum. Pls check the note, if it can help.

If MKPF only purpose is to check the posting date, can you consider implementing Note 1516684 - MKPF fields added to MSEG - Performance optimization so MSEG will contain a

BUDAT_MKPF (and other MKPF fields) and you are no longer required to read MKPF

[matt]

It's well established that in most scenarios an INNER JOIN is better performing than FOR ALL ENTRIES. Do you have any chance of combining your select from MARD with the rest of it? If you can, do it - and use an INNER JOIN.

If you find your old version works is quicker for large amounts of data, then switch back. Being quicker for small amounts of data is irrelevant, since it's quick anyway.

OR is well known to be a performance killer. I would use IN ('101', '861' ... etc). Nowadays, the optimiser can transform ORs in this way, but the more complex your SQL, the greater the chance of screwing up.

Finally, run the usual trace tools to determine if you could benefit from an index.

[Former Member]

Dear Matthew:

Thank you for your reply,

I can't use mard inner join mseg directly,

because this report needs all inventory data and calculate their aging days

[volker_borowski2]

Right Matthew !

The reason is usually, that FAE is a Software implementation of a Nested Loop Join.

The database decides dynamicly if the expected resultset is big or small, and can change the execution plan for the very same statement to a merge or hashjoin. FAE is simply not able to do that.

This is why FAE is i.g. worse that inner join, esp. for bigger resultsets.

Now there are cases, where the DB decides for a merge or hash join and depending on data volume, it might be possible that this is either a bad choice, or the DB does not have the correct information to calculate the correct plan. This is where the general advice is to calculate "fresh" db statistics, or calculate statistics with a bigger sample size, and see if something changes.

If after that the DB still comes up with a "wrong" plan, it gets interesting 🙂

You might need to change code to include hints or fake DB stats in this case, but before doing that, you should carefully analyze, how the statements behaves differently when it is fed with different parameters.

SQL Trace might be required to verify the different executiuon plans.

Best regards

Volker