Skip to Content
author's profile photo Former Member
Former Member

Help with INNER JOIN

Hi All,

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

SELECT MSEGWERKS MSEGBWART MKPFBUDAT MKPFBLDAT

MKPFBKTXT MKPFMBLNR MSEGGRUND MSEGLGORT

MSEGMATNR MSEGMENGE MSEGUMLGO MSEGSGTXT

MSEGCHARG T156TBTEXT T001WSPRAS MSEGCHARG

MSEGSHKZG MKPFCPUTM

INTO TABLE T_OUTPUT

FROM MKPF

INNER JOIN MSEG

ON MKPFMBLNR = MSEGMBLNR

AND MKPFMJAHR = MSEGMJAHR

INNER JOIN T001W

ON MSEGWERKS = T001WWERKS

INNER JOIN T156T

ON MSEGBWART = T156TBWART

AND MSEGSOBKZ = T156TSOBKZ

AND MSEGKZBEW = T156TKZBEW

AND MSEGKZZUG = T156TKZZUG

AND MSEGKZVBR = T156TKZVBR

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:27 PM

    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 😊

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:23 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.