topic Re: Help with INNER JOIN in Application Development and Automation Discussions

Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 18:05:32 GMT

Hi All,

The following statement takes quite a long time to run. Any suggestions how it can be fine tuned.

SELECT MSEG_{WERKS MSEG}BWART MKPF_{BUDAT MKPF}BLDAT

MKPF_{BKTXT MKPF}MBLNR MSEG_{GRUND MSEG}LGORT

MSEG_{MATNR MSEG}MENGE MSEG_{UMLGO MSEG}SGTXT

MSEG_{CHARG T156T}BTEXT T001W_{SPRAS MSEG}CHARG

MSEG_{SHKZG MKPF}CPUTM

INTO TABLE T_OUTPUT

FROM MKPF

INNER JOIN MSEG

ON MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR

INNER JOIN T001W

ON MSEG_{WERKS = T001W}WERKS

INNER JOIN T156T

ON MSEG_{BWART = T156T}BWART

AND MSEG_{SOBKZ = T156T}SOBKZ

AND MSEG_{KZBEW = T156T}KZBEW

AND MSEG_{KZZUG = T156T}KZZUG

AND MSEG_{KZVBR = T156T}KZVBR

WHERE MKPF~BUDAT IN S_BUDAT

AND MSEG~WERKS EQ P_WERKS

AND MSEG~XAUTO NE 'X'

AND MSEG~BWART IN S_BWART

AND MSEG~MATNR IN S_MATNR

AND T156T~SPRAS EQ SY-LANGU.

Regards

Brain

Re: Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 18:23:01 GMT

You can start by making sure that S_MATNR is not empty.

You might also try some re-arranging:

SELECT mseg~werks mseg~bwart mkpf~budat mkpf~bldat
         mkpf~bktxt mkpf~mblnr mseg~grund mseg~lgort
         mseg~matnr mseg~menge mseg~umlgo mseg~sgtxt
         mseg~charg t156t~btext t001w~spras mseg~charg
         mseg~shkzg mkpf~cputm
    INTO TABLE t_output

    FROM mseg
    INNER JOIN mkpf
       ON mkpf~mblnr = mseg~mblnr
      AND mkpf~mjahr = mseg~mjahr
    INNER JOIN t001w
       ON t001w~werks = mseg~werks
    INNER JOIN t156t
       ON  t156t~bwart = mseg~bwart
       AND t156t~sobkz = mseg~sobkz
       AND t156t~kzbew = mseg~kzbew
       AND t156t~kzzug = mseg~kzzug
       AND t156t~kzvbr = mseg~kzvbr
    WHERE mseg~matnr IN s_matnr
      AND mseg~werks EQ p_werks
      AND mseg~xauto NE 'X'
      AND mseg~bwart IN s_bwart
      AND mkpf~budat IN s_budat
      AND t156t~spras EQ sy-langu.

Rob

Message was edited by: Rob Burbank

Re: Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 18:27:46 GMT

Have you tried to run SQL trace for your program to see why this select takes so long ?

I would rather select from MKPF & MSEG via join and then select other data from t001w & T156T. In this case you can put some logic to access T001w & T156T just to retrieve data for already selected plants/movement types.

Besides, it really helps optimizer to chose right access path if you keep your select statements simple

Re: Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 18:42:22 GMT

Thanks for the response.

However usually S_MATNR is not given.

So basically the plant and the Date are the only parameters given.

Rgarding the splitting up of the inner join, i ll give it a shot.

Any other suggestions....

Regards

Brain

Re: Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 18:51:32 GMT

Then you will not be able to use an index and it will be slow.

Rob

Re: Help with INNER JOIN

Former Member — Mon, 23 Oct 2006 19:19:42 GMT

I think there is an index on BUDAT in MKPF.

Would be great if this select with join uses this index.

That is why I think it would be easier for optimizer to select right path if join is limited to MKPF/MSEG only.

OR if even MKPF/MSEG join does not use index - select from MKPF into internal table, then select from MSEG/T001W/T... using join if necessary, as this join will be by primary keys.