Performance Issue with MSEG

[MKM]

Hi,

I have a report having below logic.it is taking long time to fetch data from dictionary.

    SELECT
     KT~MBLNR  "NUMBER OF MATERIAL DOCUMENT
     KT~MJAHR  "MATERIAL DOCUMENT YEAR
     KT~BUDAT  "POSTING DATE IN THE DOCUMENT
     ST~ZEILE  "ITEM IN MATERIAL DOCUMENT
     ST~BWART  "MOVEMENT TYPE (INVENTORY MANAGEMENT)
     ST~MATNR  "MATERIAL NUMBER
     ST~WERKS  "PLANT
     ST~LGORT  "STORAGE LOCATION
     ST~CHARG  "BATCH NUMBER
     ST~SHKZG  "DEBIT/CREDIT INDICATOR
     ST~MENGE  "QUANTITY
     ST~BUKRS  "COMPANY CODE
     ST~OIVBELN " DELIVERY NO
     ST~OIPOSNR
INTO CORRESPONDING FIELDS OF TABLE GIT_MKPFMSEG_P
FROM
    ( ( (
    MKPF AS KI INNER JOIN
    MSEG AS SI ON
    KI~MANDT = SI~MANDT AND
    KI~MBLNR = SI~MBLNR AND
    KI~MJAHR = SI~MJAHR ) INNER JOIN
    MKPF AS KT ON
    KI~MANDT = KT~MANDT AND
    KI~MBLNR = KT~MBLNR AND
    KI~MJAHR = KT~MJAHR ) INNER JOIN
    MSEG AS ST ON
    SI~MANDT = ST~MANDT AND
    SI~MBLNR = ST~MBLNR AND
    SI~MJAHR = ST~MJAHR AND
    SI~ZEILE = ST~ZEILE )
    WHERE
         KI~BUDAT IN S_DATE
    AND  SI~MATNR EQ GWA_MARD-MATNR
    AND  SI~WERKS EQ GWA_MARD-WERKS
    AND  SI~LGORT EQ GWA_MARD-LGORT
    %_HINTS ORACLE 'INDEX(u201CMSEGu201D u201CMSEG~Mu201D)'.

I have gone through all most all SDN forum posts related to MSEG slowness issue and followed everything in code.for me, no INDEX is required as it is using internally standard SAP INDEX M while scanning. along with that, I have also forced it inside my WHERE clause for some hope.The above JOIN is as per SAP Note 1293807.

From BASIS end,they have updated statistics for MSEG table along with they have updated ORACLE DB profile parameters as per SAP's latest release.

after that also,performance is very slow and going for time out.MSEG has 8,200,000 entries.

pl. advice

[MKM]

Hi,

I have a report having below logic.it is taking long time to fetch data from dictionary.

    SELECT
     KT~MBLNR  "NUMBER OF MATERIAL DOCUMENT
     KT~MJAHR  "MATERIAL DOCUMENT YEAR
     KT~BUDAT  "POSTING DATE IN THE DOCUMENT
     ST~ZEILE  "ITEM IN MATERIAL DOCUMENT
     ST~BWART  "MOVEMENT TYPE (INVENTORY MANAGEMENT)
     ST~MATNR  "MATERIAL NUMBER
     ST~WERKS  "PLANT
     ST~LGORT  "STORAGE LOCATION
     ST~CHARG  "BATCH NUMBER
     ST~SHKZG  "DEBIT/CREDIT INDICATOR
     ST~MENGE  "QUANTITY
     ST~BUKRS  "COMPANY CODE
     ST~OIVBELN " DELIVERY NO
     ST~OIPOSNR
INTO CORRESPONDING FIELDS OF TABLE GIT_MKPFMSEG_P
FROM
    ( ( (
    MKPF AS KI INNER JOIN
    MSEG AS SI ON
    KI~MANDT = SI~MANDT AND
    KI~MBLNR = SI~MBLNR AND
    KI~MJAHR = SI~MJAHR ) INNER JOIN
    MKPF AS KT ON
    KI~MANDT = KT~MANDT AND
    KI~MBLNR = KT~MBLNR AND
    KI~MJAHR = KT~MJAHR ) INNER JOIN
    MSEG AS ST ON
    SI~MANDT = ST~MANDT AND
    SI~MBLNR = ST~MBLNR AND
    SI~MJAHR = ST~MJAHR AND
    SI~ZEILE = ST~ZEILE )
    WHERE
         KI~BUDAT IN S_DATE
    AND  SI~MATNR EQ GWA_MARD-MATNR
    AND  SI~WERKS EQ GWA_MARD-WERKS
    AND  SI~LGORT EQ GWA_MARD-LGORT
    %_HINTS ORACLE 'INDEX(u201CMSEGu201D u201CMSEG~Mu201D)'.

I have gone through all most all SDN forum posts related to MSEG slowness issue and followed everything in code.for me, no INDEX is required as it is using internally standard SAP INDEX M while scanning. along with that, I have also forced it inside my WHERE clause for some hope.The above JOIN is as per SAP Note 1293807.

From BASIS end,they have updated statistics for MSEG table along with they have updated ORACLE DB profile parameters as per SAP's latest release.

after that also,performance is very slow and going for time out.MSEG has 8,200,000 entries.

pl. advice

[Former Member]

Hi,

INTO CORRESPONDING FIELDS OF TABLE GIT_MKPFMSEG_P

please remove this and specify the fields in GIT_MKPFMSEG_P , means assign each fields .

Defining a structure as per the select statement .It seems to be better not to use INTO CORRESPONDING FIELDS OF.

Also think of doing that from part earlier and store it somewhere and use it in the query.It will depend upon the requirement.

Edited by: benson_10 on Feb 21, 2012 3:17 PM

[SuhaSaha]

Hello Ben,

This is a common misconception among developers that INTO CORRESPONDING FIELDS OF TABLE causes performance issues.

@Manoj: Can you explain your join condition in simple terms? Why are you using MKFP+MSEG combination twice?

BR,

Suhas

PS: You needn't use MANDT in your select query, it can safely be removed.

Edited by: Suhas Saha on Feb 21, 2012 3:29 PM

[MKM]

HI Benson/Suhas,

I have tried without using INTO CORRESPONDING FIELDS OF TABLE also.It is not giving any significant improvement.

I was thinking to store the required MSEG data in a Z table for performance improvement .But user want report in real time basis.

if you ll check SAP Note 1293807,it says the reason is for Delayed Table access. to reduce that,they are suggesting like that as per OPTIMIZER logic.

SELECT STATEMENT

|

--- FILTER

|

--- NESTED LOOPS

|

|-- NESTED LOOPS

| |-- NESTED LOOPS

1. | | |-----INDEX RANGE SCAN MKPF~BUD

2. | | -

---

INDEX RANGE SCAN MSEG~LUZ

4. | --- TABLE ACCESS BY INDEX ROWID MKPF

3. | -

---

INDEX UNIQUE SCAN MKPF~0

|

6. --- TABLE ACCESS BY INDEX ROWID MSEG

|

5. -

---

INDEX UNIQUE SCAN MSEG~0

[Former Member]

Hi Manoj,

Please check performance guidelines on join on MSEG and MKPF SAP Note 821722.

Also check with the order of fields (key fields) how you declared table.( sometimes this will increase speed)

Hi Suhas,

I can understand INTO CORRESPONDING FIELDS OF TABLE to some 500 entires will not make performance issues, but with 8,200,000 entries here i think it will have some problem.

My client is having 20M 30M enties with 160 columns and he strictly dont want me using INTO CORRESPONDING FIELDS OF TABLE . Another thing is that he dont need all column also

[http://wiki.sdn.sap.com/wiki/display/profile/2007/10/04/PerformanceofINTOCORRESPONDINGFIELDSOFinSELECTS]

Edited by: benson_10 on Feb 21, 2012 5:12 PM

Edited by: benson_10 on Feb 21, 2012 5:20 PM

[SuhaSaha]

OP wrote:

I have tried without using INTO CORRESPONDING FIELDS OF TABLE also.It is not giving any significant improvement.

I think this should put to rest your(or your client's) apprehension on using INTO CORRESPONDING FIELDS OF TABLE for tables with huge data.

Of-course you shouldn't use SELECT * for all your queries, for tables with many columns(as in your case) select only the columns you want!

BR,

Suhas

[Former Member]

Hi,

I think you are unnecessarily joining the same tables many times.

   SELECT
     KT~MBLNR  "NUMBER OF MATERIAL DOCUMENT
     KT~MJAHR  "MATERIAL DOCUMENT YEAR
     KT~BUDAT  "POSTING DATE IN THE DOCUMENT
     ST~ZEILE  "ITEM IN MATERIAL DOCUMENT
     ST~BWART  "MOVEMENT TYPE (INVENTORY MANAGEMENT)
     ST~MATNR  "MATERIAL NUMBER
     ST~WERKS  "PLANT
     ST~LGORT  "STORAGE LOCATION
     ST~CHARG  "BATCH NUMBER
     ST~SHKZG  "DEBIT/CREDIT INDICATOR
     ST~MENGE  "QUANTITY
     ST~BUKRS  "COMPANY CODE
     ST~OIVBELN " DELIVERY NO
     ST~OIPOSNR
INTO CORRESPONDING FIELDS OF TABLE GIT_MKPFMSEG_P
FROM
    MKPF AS KT INNER JOIN
    MSEG AS ST ON
    KT~MBLNR = ST~MBLNR AND
    KT~MJAHR = KT~MJAHR and
    ST~ZEILE = ST~ZEILE 
    WHERE KT~BUDAT IN S_DATE
    AND  ST~MATNR EQ GWA_MARD-MATNR
    AND  ST~WERKS EQ GWA_MARD-WERKS
    AND  ST~LGORT EQ GWA_MARD-LGORT
    %_HINTS ORACLE 'INDEX(u201CMSEGu201D u201CMSEG~Mu201D)'.

try like this a simplified version for the same query. Which i think does the same.

Hope this helps.

Aswath.

[Former Member]

Ho selective is your WHERE? IE how many rows are being returned?

Rob

[Former Member]

OIVBELN & OIPOSNR aren't in the standard MSEG. Do they relate to purchase orders, outbound deliveries or what? As MSEG as such a big table I've often found it easier to read different source tables, e.G PO History when you are looking for goods receipts.

Index M on MSEG also has BWART (movement type) in it Do you know what the possible values will be, in which case it could be added to the WHERE clause.

[Former Member]

Check the response from Aswath.

Why are you JOINing MSEG twice? I don't think I've ever seen this before and I don't see how it could help.

Also, you are mentioning the client in the JOIN conditions, but not the WHERE and the SELECT is not CLIENT DEPENDENT.

Rob

Rob

[MKM]

Hi all,

after using below code also,it is not giving me a significant change in time consumed for report execution.User want max with in 1-2 minute where MSEG has 8,200,000 entries. it is taking around 30-40 minutes & sometimes going for Time Out Dump.

SELECT
     KT~MBLNR  "NUMBER OF MATERIAL DOCUMENT
     KT~MJAHR  "MATERIAL DOCUMENT YEAR
     KT~BUDAT  "POSTING DATE IN THE DOCUMENT
     ST~ZEILE  "ITEM IN MATERIAL DOCUMENT
     ST~BWART  "MOVEMENT TYPE (INVENTORY MANAGEMENT)
     ST~MATNR  "MATERIAL NUMBER
     ST~WERKS  "PLANT
     ST~LGORT  "STORAGE LOCATION
     ST~CHARG  "BATCH NUMBER
     ST~SHKZG  "DEBIT/CREDIT INDICATOR
     ST~MENGE  "QUANTITY
     ST~BUKRS  "COMPANY CODE
     ST~OIVBELN " DELIVERY NO
     ST~OIPOSNR
INTO TABLE GIT_MKPFMSEG_P
FROM
    ( ( (
    MKPF AS KI INNER JOIN
    MSEG AS SI ON
    KI~MBLNR = SI~MBLNR AND
    KI~MJAHR = SI~MJAHR ) INNER JOIN
    MKPF AS KT ON
    KI~MBLNR = KT~MBLNR AND
    KI~MJAHR = KT~MJAHR ) INNER JOIN
    MSEG AS ST ON
    SI~MBLNR = ST~MBLNR AND
    SI~MJAHR = ST~MJAHR AND
    SI~ZEILE = ST~ZEILE )
    WHERE
         KI~BUDAT IN S_DATE
    AND  SI~MATNR EQ GWA_MARD-MATNR
    AND  SI~WERKS EQ GWA_MARD-WERKS
    AND  SI~LGORT EQ GWA_MARD-LGORT
    %_HINTS ORACLE 'INDEX(u201CMSEGu201D u201CMSEG~Mu201D)'.

simplified version of the above join, I have already used previously.But not useful.

P.N:I have tried many different logic's to minimize the load on DATABASE server while fetching data from table. without join also,if i am doing,It is stopping in MSEG line.

I don't want to restrict WHERE clause BWART wise as per requirement.

twice MSEG joining logic is described in the above mentioned SAP Note(1293807).

now I am thinking to go for MKPF statitics update as per SAP Note 821722 though it for MS SQL Server. BASIS has done for MSEG,but not so helpful.

[Former Member]

Note 1293807 is for Oracle databases only and the problem it addresses may have been solved already. Is your database Oracle?

Rob

[MKM]

yes

[volker_borowski2]

Did you execute the mentioned adjustments for the indexes from that note?

The note states the this new MB51 ist still "pilot" stuff, so the required adjustments

for the indexes might not be delivered yet. Compare the indexes to what you have in your system.

The plan you posted is from the Note! What ist the plan on your system like?

Please use the code tags when showing us the plan.

Volker

[MKM]

Hi Volker,

In our system,below following standard relevant indexes are there.

MKPF

Index ID: BUD

BUDAT Posting Date in the Document

MBLNR Number of Material Document

MSEG

Index ID: M

MATNR Material Number

WERKS Plant

LGORT Storage Location

BWART Movement Type (Inventory Management)

SOBKZ Special Stock Indicator

Index ID: OIB

MATNR Material Number

WERKS Plant

LGORT Storage Location

BWART Movement Type (Inventory Management)

SOBKZ Special Stock Indicator

OIB_TIMESTAMP Tank dip time stamp

I am using the JOIN mentioned in Page 6 of SAP Note 1293807.

I am not clear on the line which plan my system is using?

[che_eky]

What happens when you remove the JOIN mentioned in Page 6 of SAP Note 1293807??? Does the performance get noticeably worse?

Che

[MKM]

Solved By Below SAP Notes by myself

1516684: MKPF fields added to MSEG - Performance optimization

1550000: MB51: Redesign of selection for performance optimization

1558298: MB5B: Redesign of selection to optimize performance

1567602: DB dependent steps to support the redesign of MB51

1598760: FAQ: MSEG Enhancement & Redesign MB51/MB5B

thanks to all