Performance issue while selecting material documents MKPF & MSEG

[Former Member]

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

[ThomasZloch]

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

[valter_oliveira]

Maybe switching to "FROM mseg" and "JOIN mkpf" might make a difference, although unlikely

I think that this would make all the difference because you are fetching data from mkpf and then joining to mseg and actually no field is entered for restricting mkpf in for all entries.

Regards,

Valter Oliveira.

[Former Member]

Hi Thomas,

Thanks for your reply. the performance of the query has significantly improved than before after switching the join from mseg join mkpf.

Actually, I even tried without join and looped using field symbols ,this is working slightly faster than the switched join.

Here are the result , tried with 371 records as our sandbox doesn't have too many entries unfortunately ,

Results before switching the join 146036 microseconds

Results after swithing the join 38029 microseconds

Results w/o join 28068 microseconds for selection and 5725 microseconds for looping

Thanks again.

regards,

Advait

[former_member194613]

' FROM mkpf INNER JOIN mseg'

The logical order is of course different, because your WHERE condition has only conditions with mseg.

But because it is so obvious, also Thomas thought that the order in the statement will not effect the behavior.

Did you try the SQL trace for both versions and did you check the Explain? Which accesses are used?

And which database do you use? See system - status.

Siegfried