How to improve the performance in select query MSEG table by entering vendor number and PO number ?

[dineshs]

There is a requirement where report is required for Sub-Contract Vendor related stock status report along with PO number.

In input screen user will enter

Plant:(Mandatory)

Material Group:

Material:

Vendor :(Mandatory)

SLA / PO number :(Mandatory)

In output screen :

Vendor

Material

Quantity

Material group

Description

Vendor name address

Product scheduler

Purchase order / SLA

etc..

As user entering Vendor and PO number the logic that i have built is select query for fetch the records from MSEG along with movement type "541,542,543,544".

But it is taking very very long time for execution.

I tried using function module BAPI_GOODSMVT_GETITEMS but the same was unsuccessful in fetching data quickly.

Kindly help me to overcome this challange.

I even suggested user to go for entering "Posting date " for in input screen but they do not want to enter any posting date.

[NTeunckens]

See if you can find something useful in the following SAP-Notes :

• 1516684 : Enhancing MSEG with MKPF fields - performance optimization
• 1887715 : Performance optimization of the BAPI BAPI_GOODSMVT_GETITEMS

Further, do the analysis with the provided tools (SQL-trace etc.) and their solutions (creation of Index etc.) ... See the SAP-Wiki for Performance and Tuning

[Jean_Sagi]

Perhaps the query you are using give us an idea on how to enhance it.

J.

[dineshs]

Plz check the below query that you have asked,

SELECT MBLNR
ZEILE
BWART
MATNR
WERKS
LIFNR
MENGE
BUDAT
SOBKZ
CHARG
EBELN
BWTAR
FROM ZMSEG
INTO TABLE LT_ZMSEG
WHERE BWART IN ('541','542','543','544') AND
MATNR IN SE_MATNR AND
WERKS IN SE_WERKS AND
LIFNR IN SE_LIFNR AND
SOBKZ EQ 'O' AND
BWTAR IN SE_BWTAR AND
EBELN IN S_EBELN.

This is the select query i am using where the performance is very badly affected.

ZMSEG ( This is a view created using fields of "MSEG and MKPF" )

when the debugger reach this statement it takes more time to fetch the values for entering single vendor and Purchase order.

[dineshs]

SELECT MBLNR
ZEILE
BWART
MATNR
WERKS
LIFNR
MENGE
BUDAT
SOBKZ
CHARG
EBELN
BWTAR
FROM ZMSEG
INTO TABLE LT_ZMSEG
WHERE BWART IN ('541','542','543','544') AND
MATNR IN SE_MATNR AND
WERKS IN SE_WERKS AND
LIFNR IN SE_LIFNR AND
SOBKZ EQ 'O' AND
BWTAR IN SE_BWTAR AND
EBELN IN S_EBELN.

This is the select query i am using where the performance is very badly affected.

ZMSEG ( This is a view created using fields of "MSEG and MKPF" )

when the debugger reach this statement it takes more time to fetch the values for entering single vendor and Purchase order.

[ThomasZloch]

Please always post your DB (HANA or other?) and SAP_APPL release along with questions.

Not a big surprise, as neither MKPF nor MSEG provide a standard index for either LIFNR or EBELN.

Options:

1) include table EKBE in the selection logic for quick access to material documents via purchase order

2) create index for MSEG-LIFNR and/or MSEG EBELN (can make sense if several and/or critical queries would benefit)

[dineshs]

Sir I used secondary index concept and I am able to get the result faster than ever before .

thank you so much for your suggestion.

DB : Oracle

SAP_APPL: ECC 7.4

My Query has been changed as below and now working with good performance.

SELECT MBLNR
ZEILE
BWART
MATNR
WERKS
LIFNR
MENGE
BUDAT
SOBKZ
CHARG
EBELN
BWTAR
FROM ZMSEG
INTO TABLE LT_ZMSEG
WHERE BWART IN ('541','542','543','544') AND
MATNR IN SE_MATNR AND
WERKS IN SE_WERKS AND
LIFNR IN SE_LIFNR AND
SOBKZ EQ 'O' AND
BWTAR IN SE_BWTAR AND
EBELN IN S_EBELN %_hints oracle 'INDEX("MSEG" "MSEG~Z02")'.

[ThomasZloch]

Great to hear that.

Please be careful with this DB-hint. If S_EBELN is empty during runtime, you are still forcing Oracle to use that Z02 index, which is then probably not the best option.

If you remove the hint, Oracle will use Z02 anyway if S_EBELN contains selection values.