topic Re: mkpf table performance in Application Development and Automation Discussions

mkpf table performance

Former Member — Tue, 02 Dec 2008 09:05:38 GMT

here is my code . its taking 12 hours to complete for one plant which is having 14000 material for 300 days.

when i see in sm51 it is showing error in MKPF table.

any body can give any idea?? i shall be thankful.

SELECT MATNR WERKS FROM MARC INTO TABLE IT_MARC

WHERE MATNR IN S_MATNR

AND WERKS = P_WERKS.

IF IT_MARC[] IS NOT INITIAL.

SELECT MATNR MTART MATKL FROM MARA INTO TABLE IT_MARA

for all entries in IT_MARC

WHERE matnr = it_marc-matnr

and MTART in s_MTART

AND MATKL IN S_MATKL.

ENDIF.

LOOP AT IT_MARA.

IT_STOCK-MATNR = IT_MARA-MATNR.

IT_STOCK-WERKS = P_WERKS.

IT_STOCK-MTART = IT_MARA-MTART.

IT_STOCK-MATKL = IT_MARA-MATKL.

APPEND IT_STOCK.

ENDLOOP.

SELECT A_{MBLNR A}MJAHR A_{BUDAT B}ZEILE B_{MATNR B}MENGE B_{DMBTR B}SHKZG B_{WERKS B}BWART B_{XAUTO B}BUSTW B_{SJAHR B}SMBLN B~MEINS

INTO TABLE I_STK_GRGI

FROM MKPF AS A INNER JOIN MSEG AS B ON A_{MBLNR = B}MBLNR

AND A_{MJAHR = B}MJAHR

FOR ALL ENTRIES IN IT_STOCK

WHERE A~MBLNR BETWEEN '0000000001' AND '9999999999'

AND A~BUDAT BETWEEN L1_STDATE AND L_DATE

AND B~MATNR = IT_STOCK-MATNR

AND b~bwart NOT IN (311, 312)

AND B~WERKS = IT_STOCK-WERKS.

Re: mkpf table performance

h_senden2 — Tue, 02 Dec 2008 09:15:26 GMT

You don't make use of a good index for MKPF.

Why check on MBLN between 000..1 and 999.999 ? Leave it out !

Create an index on matnr and plant if it does not exist yest.

regards

Hans

Re: mkpf table performance

Former Member — Tue, 02 Dec 2008 09:30:45 GMT

First and Second Query :

Reverse the order of your query, first query on MARA and than on MARC.

Collect all materials in IT_MARA first and use it in FOR ALL ENTRIES for query on MARC.

Third Query:

Try left table as MSEG and right as MKPF. If you are having secondary index on MATNR , WERKS etc...that will be helpful. (Generally that is standard sec index provided by SAP).

If you can have P_MJAHR as parameter on screen (obligatory parameter) that will also be very helpful.

Change your query as follows

SELECT A~MBLNR A~MJAHR A~BUDAT B~ZEILE B~MATNR B~MENGE B~DMBTR B~SHKZG B~WERKS B~BWART B~XAUTO B~BUSTW B~SJAHR B~SMBLN B~MEINS
INTO TABLE I_STK_GRGI
FROM MSEG AS B INNER JOIN  MKPF AS A  ON B~MBLNR = A~MBLNR
AND B~MJAHR = A~MJAHR
FOR ALL ENTRIES IN IT_STOCK
WHERE B~MATNR = IT_STOCK-MATNR
AND B~WERKS = IT_STOCK-WERKS
AND B~MJAHR  = P_MJAHR
AND b~bwart NOT IN (311, 312) 
AND A~BUDAT BETWEEN L1_STDATE AND L_DATE.

If you can use LGORT that will be very useful, it will be able to use more keys on index M. (Check if you have secondary index with this name which has key field as MATNR, WERKS, LGORT, BWART, SOBKZ) on MSEG...

Regards,

Mohaiyuddin

Re: mkpf table performance

Former Member — Tue, 02 Dec 2008 09:32:47 GMT

You don't make use of a good index for MKPF.

how to use good index of MKPF.please let me know.

Why check on MBLN between 000..1 and 999.999 ? Leave it out !

this i am removing.

Create an index on matnr and plant if it does not exist yest.

i guess you are talking abt MSEG table.

i have some index for MSEG.

but no index is containing MATNR and WERKS only.

some indices having this field.

please elaborate.

regards

Chandramani

Re: mkpf table performance

Former Member — Tue, 02 Dec 2008 09:35:46 GMT

hi Moha..

B~MJAHR = P_MJAHR

this i did not get??

Re: mkpf table performance

Former Member — Tue, 02 Dec 2008 10:26:57 GMT

You will need to define one more parameter for YEAR - let's say P_MJAHR on your selection screen.

As MJAHR is one of the key field, this should be helpful.. Anyway if you can define LGORT as input parameter / select-option you should be able to use index on MSEG. (Provided index i had mentioned earlier is active on your table MSEG), in that case defining year as input parameter will be helpful but to limited extend

Regards,

Mohaiyuddin

Re: mkpf table performance

Former Member — Mon, 08 Dec 2008 20:56:09 GMT

Did you try the view V_MKPF to get the respective data from MKPF and MSEG?

Here are the fields of this view:

MBLNR

MJAHR

ZEILE

WERKS

VGART

BUDAT

BKTXT

Regards

Raj

Re: mkpf table performance

Former Member — Wed, 10 Dec 2008 06:36:16 GMT

thanks to all.