Performance issue while selecting material documents MKPF & MSEG
I'm facing performance issues in production while selecting Material documents for Sales order and item based on the Sales order Stock.
Here is the query :
I'm first selecting data from ebew table which is the Sales order Stock table then this query.
IF ibew IS NOT INITIAL AND ignore_material_documents IS INITIAL. * Select the Material documents created for the the sales orders. SELECT mkpf~mblnr mkpf~budat mseg~matnr mseg~mat_kdauf mseg~mat_kdpos mseg~shkzg mseg~dmbtr mseg~menge INTO CORRESPONDING FIELDS OF TABLE i_mseg FROM mkpf INNER JOIN mseg ON mkpf~mandt = mseg~mandt AND mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr FOR ALL entries IN ibew WHERE mseg~matnr = ibew-matnr AND mseg~werks = ibew-bwkey AND mseg~mat_kdauf = ibew-vbeln AND mseg~mat_kdpos = ibew-posnr. SORT i_mseg BY mat_kdauf ASCENDING mat_kdpos ASCENDING budat DESCENDING. ENDIF.
I need to select the material documents because the end users want to see the stock as on certain date for the sales orders and only material document lines can give this information. Also EBEW table gives Stock only for current date.
For Example :
If the report was run for Stock date 30th Sept 2008, but on the 5th Oct 2008, then I need to consider the goods movements after 30th Sept and add if stock was issued or subtract if stock was added.
I know there is an Index MSEG~M in database system on mseg, however I don't know the Storage location LGORT and Movement types BWART that should be considered, so I tried to use all the Storage locations and Movement types available in the system, but this caused the query to run even slower than before.
I could create an index for the fields mentioned in where clause , but it would be an overhead anyways.
Your help will be appreciated. Thanks in advance
Thomas Zloch replied
Did you make sure with an SQL Trace (ST05) that MSEG~M is really being used for the access? MATNR should actually provide a decent selectivity. Maybe switching to "FROM mseg" and "JOIN mkpf" might make a difference, although unlikely.
I assume ibew is sorted by matnr and duplicates are removed.
Reg. creating a new index, it's always a matter of weighing cost and benefit. I mean, disk space is not such a big issue any more, and the impact on processing time can be measured, and the index switched off again if there are problems. If this query is very critical, why not go for a new index.
Unless somebody comes up with another idea.