topic Re: MB51 REPORT performance in Application Development and Automation Discussions

MB51 REPORT performance

former_member605939 — Sat, 20 Jun 2020 05:05:15 GMT

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 ?

Re: MB51 REPORT performance

sanilbhandari — Sat, 20 Jun 2020 05:09:51 GMT

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

Re: MB51 REPORT performance

Dominik_Tylczynski — Sat, 20 Jun 2020 06:42:50 GMT

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

Re: MB51 REPORT performance

former_member605939 — Sat, 20 Jun 2020 08:02:58 GMT

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

Re: MB51 REPORT performance

former_member605939 — Sat, 20 Jun 2020 08:04:20 GMT

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

still we are facing the performance issue.

How to use Archive ?

Re: MB51 REPORT performance

venkateswaran_k — Sat, 20 Jun 2020 08:39:28 GMT

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

Re: MB51 REPORT performance

Dominik_Tylczynski — Sat, 20 Jun 2020 14:12:00 GMT

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

Re: MB51 REPORT performance

Abinathsiva — Sat, 20 Jun 2020 16:07:30 GMT

Hi,

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

Re: MB51 REPORT performance

sanilbhandari — Sun, 21 Jun 2020 01:55:33 GMT

Hello abaplearner

Please read through the SAP help for the steps for archival

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

Thanks & Regards

Sanil Bhandari

Re: MB51 REPORT performance

gasparerdelyi — Mon, 22 Jun 2020 08:42:55 GMT

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.

Re: MB51 REPORT performance

former_member605939 — Fri, 26 Jun 2020 06:35:49 GMT

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

Re: MB51 REPORT performance

former_member605939 — Fri, 26 Jun 2020 11:58:08 GMT

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

Re: MB51 REPORT performance

Sandra_Rossi — Sat, 27 Jun 2020 08:22:20 GMT

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.

Re: MB51 REPORT performance

former_member605939 — Mon, 29 Jun 2020 09:55:33 GMT

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.

Re: MB51 REPORT performance

former_member605939 — Tue, 30 Jun 2020 05:15:19 GMT

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.

Re: MB51 REPORT performance

venkateswaran_k — Tue, 30 Jun 2020 05:52:39 GMT

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

Re: MB51 REPORT performance

venkateswaran_k — Tue, 30 Jun 2020 06:00:23 GMT

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

Re: MB51 REPORT performance

Dominik_Tylczynski — Tue, 30 Jun 2020 06:37:39 GMT

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

Re: MB51 REPORT performance

former_member456187 — Tue, 30 Jun 2020 07:17:34 GMT

Mkpf one day records are 11,000 approx

Re: MB51 REPORT performance

Dominik_Tylczynski — Tue, 30 Jun 2020 07:29:36 GMT

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.