Indexing for table J_3ABSSI helped to improve /AFS/MD04 performance
When material planners are planning the AFS grid materials they refer stock requirement list tcode /AFS/MD04 very often. And if the materials are having huge planning data (Planned order, purchase requisitions, purchase orders, reservations, sales orders etc.) in /AFS/MD04 then the system was taking significant time to retrieve the information from the database. This was the case for one material and planners are dealing with a number of such materials daily. Effectively this issue was affecting overall planning process plus a major delay in the process and creating planners’ job monotonous.
Possible options to optimize the process
Some of the alternate options identified
- Execute /AFS/MD04 tcode in a certain way – Using a specific date and stock type helped to reduce the runtime. But the problem with this option was, the user can search based on only one stock type e.g. stock type B-Purchase Order or F-Production Order.
- Archiving the data – We thought of archiving option for the data that is not being used since a long time e.g. purchasing documents/ purchase orders, purchase requisitions. But archiving was touching very small part of the whole data. This option was also not supportive.
- To resolve the MD04 runtime issue, we tried to search for SAP notes (if there is any) that can fix the problem. But no any SAP note available that can have a solution to fix the performance issue.
- With the help of basis team, we performed the system trace for this screen and found that there was a database query that is running against table J_3ABSSI in the system and that has 166 million records to check every time.
System trace – When we found that /AFS/MD04 is taking longer than usual time for execution then we performed the system trace. And during the trace we got to know that there was a database query running against database table J_3ABSSI and that has 166 million records to check every time.
Field sequence and other indexing – To optimize the performance we checked the details of other indexes and their field sequences. We made sure that the sequence that we are going to use in the index should be different than other six indexes.
We created new index Z05 with new set/sequence of fields. Tcodes used – SE11
Performed /AFS/MD04 execution by using FM – MD_STOCK_REQUIREMENTS_LIST_API before and after new index Z05. Before new index system took 388.8 seconds for executing /AFS/MD04 for one material and for the same material it took 3.99 seconds after new index Z05.
Before new index Z05
After new index Z05
Regression testing scenarios –
Performed APO planning and checked the table J_3ABSSI updates after planning results and data transfer to ECC from APO.
Checked table updates for AFS material PO after inventory changes. Also monitored /AFS/MD04 changes.