MB51 REPORT performance

former_member605939 · ‎2020 Jun 20

We are facing the MB51 report performance issue . Already implemented SAP NOTE :

1516684 - Enhancing MSEG with MKPF fields - performance optimization. Still we are facing the long time to get the output. What are other things that we consider to increase the performance ?

sanilbhandari · ‎2020 Jun 20

Hello abaplearner

Please check the OSS Note for various steps you can take to improve performance of MB51

https://launchpad.support.sap.com/#/notes/0001915056

Thanks & Regards,

Sanil Bhandari

former_member605939 · ‎2020 Jun 20

Gone through the https://launchpad.support.sap.com/#/notes/0001915056

A. Selection Criteria : We are following.

B. Reduce the number of data fields processed by RM07DOCS (MB51) : We are not following

C. De-activate colours from MB51 :We are not following

D. Archive :We are not following

E. Implement MB51 redesign(available from software component SAP APPL 600) :We implemented and following

How much performance degraded with Point B,C,D .

And How to use Point D

Thanks

former_member605939 · ‎2020 Jun 26

After debugging mb51, observed that time consumed on SELECT (G_T_FIELDS_NEW)

    INTO CORRESPONDING FIELDS OF TABLE ITAB

    FROM MKPF INNER JOIN MSEG

    ON    MKPF~MANDT = MSEG~MANDT

      AND MKPF~MBLNR = MSEG~MBLNR

      AND MKPF~MJAHR = MSEG~MJAHR

      CONNECTION (DBCON)

       WHERE MSEG~BUDAT_MKPF IN BUDAT

         AND MSEG~BWART IN BWART

         AND MSEG~CHARG IN CHARG

         AND MSEG~CPUDT_MKPF IN CPUDT

         AND MSEG~CPUTM_MKPF IN CPUTM

         AND MSEG~KOSTL IN KOSTL

         AND MSEG~KUNNR IN KUNNR

         AND MSEG~LGORT IN LGORT

         AND MSEG~LIFNR IN LIFNR

         AND MSEG~MATNR IN MATNR

         AND MSEG~MAT_KDAUF IN MAT_KDAU

         AND MSEG~MAT_KDPOS IN MAT_KDPO

         AND MSEG~MBLNR IN MBLNR

         AND MSEG~MJAHR IN MJAHR

         AND MSEG~SOBKZ IN SOBKZ

         AND MSEG~USNAM_MKPF IN USNAM

         AND MSEG~VGART_MKPF IN VGART

         AND MSEG~WERKS IN WERKS

         AND MSEG~XBLNR_MKPF IN XBLNR


and LOOP AT LIST.

      MOVE-CORRESPONDING LIST TO EXPORT_LIST.

      APPEND EXPORT_LIST.

    ENDLOOP.

analysed the time consuming statements ....still looking for faster technique

DominikTylczyn · ‎2020 Jun 20

What is the year MKPF-MJAHR field of your oldest documents?

Have you considered archiving? It improves performance tremendously and it’s not that difficult.

Best regards

Dominik Tylczynski

former_member605939 · ‎2020 Jun 20

We are using 3 months to 6 months data of year 2019.

still we are facing the performance issue.

How to use Archive ?

sanilbhandari · ‎2020 Jun 21

Hello abaplearner

Please read through the SAP help for the steps for archival

https://help.sap.com/viewer/b704a8db767040a08100adc846218964/6.17.17/en-US/75bcb6531de6b64ce10000000...

Thanks & Regards

Sanil Bhandari

venkateswaran_k · ‎2020 Jun 20

Hi

As long as you are having more data that are not archived, You will face this issue.

Please request the users Or customize the report to do the following

You entered the Material / Material Range
You entered the Plant ./ Plant Range
Most importantly the Date range. (at least this is mandatory)

Regards,

Venkat

DominikTylczyn · ‎2020 Jun 20

Hello abaplearner

The question is not how much data you are using but how many material documents you keep in your system. If that number is substantial it is going to slow down all MM reporting including MB51.

You can archive material documents with SARA transaction. The process might be time consuming if you have a lot of material documents to archive but conceptually it's straight forward. Performance gain after archiving is substantial - been there, done that.

Best regards

Dominik Tylczynski

former_member605939 · ‎2020 Jun 30

While testing the performance of mb51 based on mkpf and mseg , created an index on mkpf for budat and mjahr . And checked the report output. Performance increased and desired result. Before the index the report was taking 40 to 60 minutes and checked the performance after index with same parameters , the report output was generated with in 5 to 10 minutes. But the effect came on MB5B report as its performance totally degraded. its taking to long time to display one day output for single material ( 15 to 20 minutes ) earlier the output was within seconds.

Because both the reports are based on MKPF AND MSEG tables. so i deleted that index from mkpf. but still MB5Btaking long time for single material and one day date.

venkateswaran_k · ‎2020 Jun 30

Hi

You said after creating the index, the MB51 gives output in 5 to 10 min. However the MB5B got impact and it become slower than before.

Then you revert back the index. Even after the MB5B is still slow. Did you check again MB51 ? is that become slower as well? Then you can conclude it is because of Index.

I would prefer, as I said in other related issue of yours, make those fields mandatory for selection.

Regards,

Venkat

venkateswaran_k · ‎2020 Jun 30

Also, Please using ST05 - make the trace on and see which table and which index are consuming time for both the reports

Abinathsiva · ‎2020 Jun 20

Hi,

Try passing more parameters and filter and narrow down the search parameter....

gasparerdelyi · ‎2020 Jun 22

0. have you also finished the conversion and wrote the control table entry?
1. do you have any enhancements that query data from a join on MSEG and MKPF? If yes, are those queries in enhancements adjusted?
2. There are a handful of other corrections, have you done a search on SAP Notes and evaluated them?
3. have you taken traces by SAT/SE30/ST05/ST12? (ST12 comes with ST-A/PI, can take ABAP and SQL/RFC/ENQ trace at the same time, but SAT+ST05 should be also perfectly enough) -- by analyying these traces one can then tell where is the time spent.

former_member605939 · ‎2020 Jun 26

After debugging mb51, observed that time consumed on SELECT (G_T_FIELDS_NEW)

    INTO CORRESPONDING FIELDS OF TABLE ITAB

    FROM MKPF INNER JOIN MSEG

    ON    MKPF~MANDT = MSEG~MANDT

      AND MKPF~MBLNR = MSEG~MBLNR

      AND MKPF~MJAHR = MSEG~MJAHR

      CONNECTION (DBCON)

       WHERE MSEG~BUDAT_MKPF IN BUDAT

         AND MSEG~BWART IN BWART

         AND MSEG~CHARG IN CHARG

         AND MSEG~CPUDT_MKPF IN CPUDT

         AND MSEG~CPUTM_MKPF IN CPUTM

         AND MSEG~KOSTL IN KOSTL

         AND MSEG~KUNNR IN KUNNR

         AND MSEG~LGORT IN LGORT

         AND MSEG~LIFNR IN LIFNR

         AND MSEG~MATNR IN MATNR

         AND MSEG~MAT_KDAUF IN MAT_KDAU

         AND MSEG~MAT_KDPOS IN MAT_KDPO

         AND MSEG~MBLNR IN MBLNR

         AND MSEG~MJAHR IN MJAHR

         AND MSEG~SOBKZ IN SOBKZ

         AND MSEG~USNAM_MKPF IN USNAM

         AND MSEG~VGART_MKPF IN VGART

         AND MSEG~WERKS IN WERKS

         AND MSEG~XBLNR_MKPF IN XBLNR


and LOOP AT LIST.

      MOVE-CORRESPONDING LIST TO EXPORT_LIST.

      APPEND EXPORT_LIST.

    ENDLOOP.

analysed the time consuming statements ....still looking for faster technique

Sandra_Rossi · ‎2020 Jun 27

How much time does this SQL query takes?

Please provide the Execution Plan (in ST05 or ST04) corresponding to this query so that we can help you in telling you what exactly happens.

NB: rather than using debug, if you use an ABAP trace, you would know immediately, after the program has finished running, what statements take time.

former_member605939 · ‎2020 Jun 29

While testing the performance of mb51 based on mkpf and mseg , created an index on mkpf for budat and mjahr . And checked the report output. Performance increased and desired result. Before the index the report was taking 40 to 60 minutes and checked the performance after index with same parameters , the report output was generated with in 5 to 10 minutes. But the effect came on MB5B report as its performance totally degraded. its taking to long time to display one day output for single material ( 15 to 20 minutes ) earlier the output was within seconds.

Because both the reports are based on MKPF AND MSEG tables. so i deleted that index from mkpf. but still MB5B taking long time for single material and one day date.

DominikTylczyn · ‎2020 Jun 30

Hello abaplearner

Performance tuning is a tricky topic - you could make PhD out of that.

Indexing MKPF and MSEG tables is not a silver bullet solution to MB51 performance. MB51 is a very flexible report in terms of selection criteria - notice it features a selection screen full of criteria. Moreover these selection criteria can be further customized in SPRO: Material Management -> Inventory Management and Physical Inventory -> Reporting -> Define Field Selection for Material Document List. Depending on what criteria are used, different MKPF/MSEG indexes will be used. So unless you have strict rules about which selection criteria are used, one additional index won't help you.

Indexing tables has a side effect on write operation - the more indexes you have to slower are write operations as database has to maintain the indexes. So you might notice adverse effect on material documents postings when you create more indexes on MKPF/MSEG.

Let me reiterate - consider material documents archiving. That will boost MB51 performance, as well as performance of all other materials management operations and reports. You haven't answer my questions on MKPF/MSEG data volume you are facing.

Best regards

Dominik Tylczynski

former_member456187 · ‎2020 Jun 30

Mkpf one day records are 11,000 approx

DominikTylczyn · ‎2020 Jun 30

MKPF records per day doesn't help.

Check total number of records in MKPF and total number of records in MSEG.

Check the lowest year MJAHR in MKPF.

That would help.

former_member605939 · ‎2020 Jun 30

YEAR 2019 : 3,177,526 records in mkpf. we have implemented 1516684 - Enhancing MSEG with MKPF fields - performance optimization.

Moreover if you check include RM07MLBD_FORM_02. it is called the 

SELECT (g_t_mseg_fields)

         INTO CORRESPONDING FIELDS OF TABLE g_t_mseg_lean

         FROM mkpf AS mkpf  JOIN mseg AS mseg

                  ON mkpf~mandt  =  mseg~mandt  AND

                     mkpf~mblnr  =  mseg~mblnr  AND

                     mkpf~mjahr  =  mseg~mjahr

         CONNECTION (dbcon)                                 "n1710850

         WHERE mseg~matnr  IN  matnr

           AND mseg~werks  IN  g_ra_werks

           AND mseg~lgort  IN  g_ra_lgort

           AND mseg~charg  IN  charg

           AND mseg~bwtar  IN  bwtar

           AND mseg~bwart  IN  bwart

           AND mkpf~budat  GE  datum-low

           AND mseg~sobkz  IN  g_ra_sobkz

    %_HINTS                                   "#EC CI_HINTS "n1511550 "commented for testing 28.06.2020

    DB2    '&SUBSTITUTE VALUES&'

    ORACLE '&SUBSTITUTE VALUES&'.

but as we have implemented the note 1516684
It is not calling 


SELECT (G_T_MSEG_FIELDS)                                "n1558298

         INTO CORRESPONDING FIELDS OF TABLE G_T_MSEG_LEAN   "n1558298

         FROM MKPF AS MKPF  JOIN MSEG AS MSEG               "n1558298

                  ON MKPF~MANDT  =  MSEG~MANDT  AND         "n1558298

                     MKPF~MBLNR  =  MSEG~MBLNR  AND         "n1558298

                     MKPF~MJAHR  =  MSEG~MJAHR              "n1558298

         CONNECTION (dbcon)                                 "n1710850

         WHERE MSEG~MATNR       IN  MATNR                   "n1558298

           AND MSEG~WERKS       IN  G_RA_WERKS              "n1558298

           AND MSEG~LGORT       IN  G_RA_LGORT              "n1558298

           AND MSEG~CHARG       IN  CHARG                   "n1558298

           AND MSEG~BWTAR       IN  BWTAR                   "n1558298

           AND MSEG~BWART       IN  BWART                   "n1558298

           AND MSEG~BUDAT_MKPF  GE  DATUM-LOW               "n1558298

           AND MSEG~SOBKZ       IN  G_RA_SOBKZ              "n1558298

    %_HINTS                                   "#EC CI_HINTS "n1558298

    DB2    '&SUBSTITUTE VALUES&'                            "n1558298

    ORACLE '&SUBSTITUTE VALUES&'.

Here it calling MSEG~BUDAT_MKPF  GE  DATUM-LOW   and in above case  mkpf~budat  GE  datum-low

DominikTylczyn · ‎2020 Jun 30

Again, can you share the following data:

Total number (all years) of records in MKPF
Total number (all years) of records in MSEG
When did you start posting material documents? What is the posting date or MJAHR field value of your oldest material document?

former_member605939 · ‎2020 Jun 30

Total number (all years) of records in MKPF = 23,261,121
Total number (all years) of records in MSEG = 86,125,565
When did you start posting material documents? What is the posting date or MJAHR field value of your oldest material document? = 01.04.2000 (year 2000)

former_member605939 · ‎2020 Jun 30

sql-trace-log.txt

Dominik Tylczynski ,Sandra Rossi, Venkateswaran (Venkat) Krishnamurthy

As per required i am attaching sql trace.

former_member605939 · ‎2020 Jul 01

Plz let me know the best performance increase solution in which both MB51 and MB5B reports get faster.I have provided the index details and st05 analysis.

DominikTylczyn · ‎2020 Jul 02

Hello ABAP LEARNER

See, you are keeping 20-years worth of data in your system i.e. 86M records in MSEG. However you need only 6-months of that for your reporting. You could definitively shave off much of that with archiving and this way boost performance of all MM reports without any technical tweaks.

Best regards

Dominik Tylczynski

Sandra_Rossi · ‎2020 Jul 02

Please provide the Execution Plan of the concerned SQL statement, not the general trace.