Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Performance issue while selecting material documents MKPF & MSEG

Hello,

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

regards,

Advait

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.

Thomas

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question