2010 Sep 08 3:30 PM
Hi!
I'm with a problem in a selection of mkpf and mseg tables. I need to select some period of MKPF (i'm using the field budat) and after, select the MSEG table (of some materials).
I tried many diferent commands such as "Inner join" and "for all entries". In the case of "for all entries", the program blocked the mseg table many times and in the case of "inner join", the cost wasn't very good ... I had the help of a DBA Oracle, too. But when I wrote the command in ABAP, the execution was terrible !!!!
Somebody in this forum have the same problem any time? Do you know something to help me?
The command that I'm testing now is (after the help of the DBA Oracle):
SELECT amblnr bmjahr bzeile bmatnr bwerks blgort bcharg bshkzg b~menge
INTO CORRESPONDING FIELDS OF TABLE t_mseg
FROM mkpf AS a
INNER JOIN mseg AS b ON amblnr = bmblnr AND
amjahr = bmjahr
WHERE a~budat IN r_bldat AND
b~matnr IN r_matnr AND
b~werks = l_centro.
2010 Sep 08 4:48 PM
Std. SAP index BUD on table MKPF should be used in your query if the date range is not empty. In transaction SE11 or SE12 check to see in this index is active in your database.
TYPES: BEGIN OF ty_mseg,
mblnr TYPE mseg-mblnr,
mjahr TYPE mseg-mjahr,
zeile TYPE mseg-zeile,
matnr TYPE mseg-matnr,
werks TYPE mseg-werks,
lgort TYPE mseg-lgort,
charg TYPE mseg-charg,
shkzg TYPE mseg-shkzg,
menge TYPE mseg-menge,
END OF ty_mseg.
DATA: t_mseg TYPE TABLE OF ty_mseg.
IF NOT r_bldat[] IS INITIAL.
SELECT b~mblnr
b~mjahr
b~zeile
b~matnr
b~werks
b~lgort
b~charg
b~shkzg
b~menge
FROM mkpf AS a
INNER JOIN mseg AS b
ON a~mblnr = b~mblnr
AND a~mjahr = b~mjahr
INTO TABLE t_mseg
WHERE a~budat IN r_bldat
AND b~matnr IN r_matnr
AND b~werks EQ l_centro.
ELSEIF NOT r_matnr[] IS INITIAL.
SELECT mblnr
mjahr
zeile
matnr
werks
lgort
charg
shkzg
menge
FROM mseg
INTO TABLE t_mseg
WHERE matnr IN r_matnr
AND werks EQ l_centro.
ENDIF.
2010 Sep 08 4:48 PM
Std. SAP index BUD on table MKPF should be used in your query if the date range is not empty. In transaction SE11 or SE12 check to see in this index is active in your database.
TYPES: BEGIN OF ty_mseg,
mblnr TYPE mseg-mblnr,
mjahr TYPE mseg-mjahr,
zeile TYPE mseg-zeile,
matnr TYPE mseg-matnr,
werks TYPE mseg-werks,
lgort TYPE mseg-lgort,
charg TYPE mseg-charg,
shkzg TYPE mseg-shkzg,
menge TYPE mseg-menge,
END OF ty_mseg.
DATA: t_mseg TYPE TABLE OF ty_mseg.
IF NOT r_bldat[] IS INITIAL.
SELECT b~mblnr
b~mjahr
b~zeile
b~matnr
b~werks
b~lgort
b~charg
b~shkzg
b~menge
FROM mkpf AS a
INNER JOIN mseg AS b
ON a~mblnr = b~mblnr
AND a~mjahr = b~mjahr
INTO TABLE t_mseg
WHERE a~budat IN r_bldat
AND b~matnr IN r_matnr
AND b~werks EQ l_centro.
ELSEIF NOT r_matnr[] IS INITIAL.
SELECT mblnr
mjahr
zeile
matnr
werks
lgort
charg
shkzg
menge
FROM mseg
INTO TABLE t_mseg
WHERE matnr IN r_matnr
AND werks EQ l_centro.
ENDIF.
2010 Sep 08 6:50 PM
Mark,
thanks for the help.
But, analysing the indexes by SE11 e SE12, all the indexes are actives in my system. And the variables aren't empty when the program executes the select command.
2010 Sep 08 7:12 PM
What does your trace show? Mark is right; that SELECT should pick up MKPFBUD then go for MSEG0 and be fairly quick depending on your selections and statistics spread, unless you have some bad indexes that are confusing the optimizer. How does the performance compare with MB51? That's nearly the same SELECT as that transaction.
2010 Sep 09 5:39 AM
Hi Thyra,
I am agree with Mark solution, but you should used the %hint statement because some time index analyser not pick the proper index. You should hard code index for above scenario.
2010 Sep 09 2:35 PM
you should used the %hint statement
I would disagree - hints should only be used as a last resort. 99% of the time, performance problems are due to bad coding, improperly created or too many indexes, stale statistics, etc. Most developers (and some DBA's) are not experienced enough to override the choice of the optimizer.
2010 Sep 11 2:07 PM
Hi Brad Bohn,
Sorry dear, I would like to tell Join in mkpf and mseg is not recommend, In first case it should be take index "BUT" of mkpf but you are putting value in both date as well as material, which index will optimizer will pick up any of the index from "BUT" of mkpf or "M" of mseg. As of my analysis of index in this case it must be take "BUT". But what will happned If it pick up "M", it not resolve the problem.
and in second case it must be pick up the index "M" where date in not consider.