2010 Jan 09 9:30 AM
hi friends, we are using mb5b transaction.but it is very slow. I want to reindex the table MKPF :
SELECT
T_00 . "BLART" , T_00 . "BUDAT" , T_00 . "CPUDT" , T_00 . "CPUTM" , T_00 . "MBLNR" , T_00 . "MJAHR" ,
T_00 . "USNAM" , T_00 . "VGART" , T_00 . "XABLN" , T_01 . "BUSTM" , T_01 . "BUSTW" , T_01 . "BWART" ,
T_01 . "BWTAR" , T_01 . "CHARG" , T_01 . "DMBTR" , T_01 . "DMBUM" , T_01 . "INSMK" , T_01 . "KZBEW" ,
T_01 . "KZBWS" , T_01 . "KZVBR" , T_01 . "KZZUG" , T_01 . "LGORT" , T_01 . "MATNR" , T_01 . "MEINS" ,
T_01 . "MENGE" , T_01 . "MENGU" , T_01 . "SHKZG" , T_01 . "SJAHR" , T_01 . "SMBLN" , T_01 . "SMBLP" ,
T_01 . "SOBKZ" , T_01 . "WAERS" , T_01 . "WERKS" , T_01 . "WERTU" , T_01 . "XAUTO" , T_01 . "ZEILE"
FROM
"MKPF" T_00 INNER JOIN "MSEG" T_01 ON T_01 . "MANDT" = '300' AND T_00 . "MANDT" = T_01 . "MANDT" AND T_0
0 . "MBLNR" = T_01 . "MBLNR" AND T_00 . "MJAHR" = T_01 . "MJAHR"
WHERE
T_00 . "MANDT" = '300' AND T_01 . "MATNR" = '000000000013000000' AND T_01 . "WERKS" = '460A' AND T_01 .
"LGORT" <> ' ' AND T_00 . "BUDAT" >= '20091001' AND T_01 . "SOBKZ" = ' '#
the system is showing above select stement last read.
plz help me how to reindex the MKPF and solve the problem
2010 Jan 10 11:29 PM
Hi,
1) The fact that that it is the last statement doesn't mean that's where the problem lies. I would check with SE30 where the time is really being spent.
2) There is an index MSEGM by MANDT + MATNR + WERKS (+ LGORT + BWART + SOBKZ), so there is not much to improve. Check with ST05 if MSEGM is being used for this query. If it is, ask your DBA to rebuild the index and check if there is any improvement.
3) Index MSEG~M might not be good enough if there are materials with a very big number of movements (in the same plant). I have had the same problem, and to solve it (in Z reports) we had to create a redundant table that has some fields from MKPF and MSEG (so that we can filter not also by MATNR and WERKS, but also by BUDAT).
2010 Jan 11 4:36 AM
Hi Rui, thnx fr ur help friend.I gt ur point.But there is one confusion are you suggesting to develop a new report for this so that we can filter by BUDAT also .But dear selection date fild is already provided in the MB5B screen
2010 Jan 11 11:04 AM
Hi.
What I meant is that you won't be able to improve MB5B with an additional index (for the criteria you have); you could check with user if he can fill the storage location (and the movement type if possible).
Have you checked if MSEG~M is being used?
Can you post here the explain plan and index statistics?
Rui Dantas
2010 Jan 11 11:37 AM
kk i will try.....actually the problm is only with a particular material no.