Select - MKPF x MSEG

[thayra_pedroso]

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 a_{mblnr b}mjahr b_{zeile b}matnr b_{werks b}lgort b_{charg b}shkzg b~menge

INTO CORRESPONDING FIELDS OF TABLE t_mseg

FROM mkpf AS a

INNER JOIN mseg AS b ON a_{mblnr = b}mblnr AND

a_{mjahr = b}mjahr

WHERE a~budat IN r_bldat AND

b~matnr IN r_matnr AND

b~werks = l_centro.

[Former Member]

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.

[thayra_pedroso]

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.

[brad_bohn]

What does your trace show? Mark is right; that SELECT should pick up MKPF_{BUD then go for MSEG}0 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.

[ravi_lanjewar]

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.

[brad_bohn]

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.

[ravi_lanjewar]

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.