Performance problem reading MKPF

[Former Member]

Hello SAPients.

I have to modify a program to improve the performance of it. I've analysed and found that the problem occurs when the program reads table MKPF in this query:

select * from mkpf

into table i_mkpf

where bktxt = i_vttk-tknum.

BKTXT is a free form text field and of course MKPF doesn't have an index on this field. I've been searching for other way to go from the shipment to the material document but I can't find the right tables to establish a relation among them and improve the performance of this report. Does anybody knows the tables and their relations to go from the shipment number to the material document?

Thank you very much for your help.

[Former Member]

Hi..

You can solve the below sql.

VBFA table has information about Documentary flow.

I think that VBFA is the bridge.

SELECT c.*

FROM sapr3.VTTP a, sapr3.VBFA b, sapr3.MKPF c

WHERE a.mandt = '100'

AND a.tknum = '0034734699'

AND b.mandt = a.mandt

AND b.vbelv = a.vbeln

AND b.vbtyp_n = 'R'

AND c.mandt = b.mandt

AND c.mblnr = b.vbeln;

Also, I think that You had better create an index of mkpf(bktxt field).

Good luck!!

[Former Member]

Hi Kil Nam Kim.

I tried your solution but it didn't work. The query doesn't return the same records as the original query on BKTXT.

I can't create an index on BKTXT because of the policies of the company.

Anybody with other idea?

[Former Member]

select * from mkpf

into table i_mkpf

where bktxt = i_vttk-tknum.

do you need all the fields...

if not then try to select only the required fields,

that will reduce some load, create an internal table with required fields and select them to that table...

try and check the performance...

[Former Member]

Hi,

Create Index for BKTXT in MKPF. IF creating index is an issue . Please let me know then I will try to provide other solution .

[Former Member]

I also have the same performance issue with this Query, So Creating Index on BKTXT will resolve ur problem this is the only solution for this query....

[Former Member]

i think through shipment number you would be getting outbound delivery as reference, so this delivery number coudl be used as reference. You can try querying on the purchase order history documents to get the material document number.

[RichHeilman]

I'd really hate to tell you to pick up the whole table, but it may be faster. Test it out and see.

select * from mkpf
     into table i_mkpf.

delete from i_MKPF where bktxt <> i_vttk-tknum.

REgards,

Rich Heilman

[Former Member]

try to follow this.

from VTTK table then

1) GET ALL shipment items , here you will have VBELN(this is nothing but Delivery no)

2) then Goto LIPS & get VBELN by passing VTTP-VBELN.

3) use this LIPS-VBELN get LIKP details(both have Delivery nos)

4)then use this LIKP-VBELN & for MKPF-VBELN.

for example :

this code is in reverse order of your requirement.

(i.e, i am starting for MKPF & getting VTTK details)

SELECT MBLNR

MJAHR

XBLNR

INTO TABLE IT_MKPF_TMP

FROM MKPF

WHERE VGART = 'WL'

AND CPUDT IN S_DATUM.

IF SY-SUBRC <> 0.

EXIT.

ENDIF.

LOOP AT IT_MKPF_TMP.

IT_MKPF1-VBELN = IT_MKPF_TMP-XBLNR+0(10).

IT_MKPF1-MJAHR = IT_MKPF_TMP-MJAHR.

APPEND IT_MKPF1.

CLEAR : IT_MKPF1,

IT_MKPF_TMP.

ENDLOOP.

SORT IT_MKPF1 BY VBELN.

DELETE ADJACENT DUPLICATES FROM IT_MKPF1 COMPARING VBELN.

SELECT A~VBELN

A~TRAID

A~BTGEW

A~KODAT

A~KUNNR

A~ANZPK

A~ROUTA

A~WADAT

A~LIFNR

A~VSTEL

A~LFART

A~KOUHR

A~WADAT_IST

A~LFDAT

A~BOLNR

INTO TABLE IT_LIKP

FROM LIKP AS A JOIN VBUK AS B

ON A_{VBELN = B}VBELN

FOR ALL ENTRIES IN IT_MKPF1

WHERE A~VBELN IN S_VBELN

AND A~VBELN = IT_MKPF1-VBELN

AND A~VSTEL IN S_WERKS

AND B~WBSTK = 'C'

AND A~LFART NE C_LFART

AND A~LFART NE C_LFART1.

IF IT_LIKP[] IS INITIAL.

EXIT.

ENDIF.

SORT IT_LIKP BY VBELN.

*--Get Delivery Line items

SELECT VBELN

POSNR

WERKS

LGORT

CHARG

MATNR

LFIMG

VGBEL

MFRGR

MEINS

VRKME

UMVKZ

UMVKN

UMWRK

INTO TABLE IT_LIPS

FROM LIPS

FOR ALL ENTRIES IN IT_LIKP

WHERE VBELN = IT_LIKP-VBELN

AND CHARG <> ''

AND MTART EQ C_MTART.

IF IT_LIPS[] IS INITIAL.

EXIT.

ENDIF.

*--Get Shipment Details.(VTTP)

SELECT TKNUM

VBELN

INTO TABLE IT_VTTP

FROM VTTP

FOR ALL ENTRIES IN IT_LIKP

WHERE VBELN = IT_LIKP-VBELN.

*--Get Shipment Header details.(VTTK)

IF SY-SUBRC = 0.

SELECT TKNUM

TPLST

TDLNR

DTABF

DATEN

EXTI2

INTO TABLE IT_VTTK

FROM VTTK

FOR ALL ENTRIES IN IT_VTTP

WHERE TKNUM = IT_VTTP-TKNUM.

SORT IT_VTTK BY TKNUM.

ENDIF.

[Former Member]

To start - your SQL is selecting INTO TABLE. Are you expecting multiple records (possibly)? If not, do a SELECT SINGLE to stop the DB serach.

If you are expecting multiple records, here is the relationship that should get you thru:

VTTK is the shipment header. VTTP is the shipment detail, which includes the Delivery number (VBELN). There is a secondary index on VTTP-VBELN.

So... use the shipment number and get the delivery from VTTP. Then go to VBFA (Doc flow table) using the delivery number to get the material document number (note set vbtyp_n = 'R' --> Goods Movement... this is your material doc number).

Please reward those points!!!

[Former Member]

hi recardo,

what happened? is your problem solved ?? or still facing it??

pl let us know.

thanks

srikanth