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

Help with INNER JOIN

Former Member — Thu, 27 Jul 2006 23:34:30 GMT

Hi All,

I am using the following INNER JOIN statement, which takes a long time.

Can anybody let me know how to fine tune this statement.

SELECT a_{mtpos b}matnr b_{mtvfp b}werks

b_{dismm b}beskz b_{plifz b}disgr

c_{pstat c}mtart c_{brgew c}normt

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mvke AS a INNER JOIN marc AS b

ON a_{matnr = b}matnr INNER JOIN mara AS c ON a_{matnr = c}matnr

FOR ALL ENTRIES IN t_materials2

WHERE a~matnr EQ t_materials2-matnr

AND a~vkorg EQ p_vkorg

AND a~vtweg EQ p_vtweg

AND a~mtpos IN s_mtpos

AND b~werks EQ t_materials2-werks

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND b~mmsta EQ '03'

AND c~mtart IN s_mtart.

Thanks

Brain

Re: Help with INNER JOIN

abdul_hakim — Thu, 27 Jul 2006 23:47:03 GMT

You could split your SELECT Clause as follows..

IF NOT I_MATERIALS[] IS INITIAL.

SELECT MATNR MTPOS FROM MVKE INTO TABLE ITAB_MVKE FOR ALL ENTRIES IN T_MATERIALS2 WHERE MATNR EQ T_MATERIALS-MATNR AND VKORG EQ P_VKORG AND VTWEG EQ P_VTWEG AND MTPOS IN S_MTPOS.

ENDIF.

IF NOT T_MATERIALS[] IS INITIAL.

SELECT MATNR MTVFP WERKS DISMM BESKZ PLIFZ DISGR FROM MARC INTO TABLE ITAB_MARC FOR ALL ENTRIES IN I_MATERIALSWHERE MATNR EQ T_MATERIALS-MATNR AND

WERKS EQ T_MATERIALS-WERKS AND

stawn IN s_stawn

AND disgr IN s_disgr

AND mmsta EQ '03'.

ENDIF.

IF NOT T_MATERIALS[] IS INITIAL.

SELECT MATNR PSTAT MTART BRGEW NORMT FROM MARA INTO TABLE ITAB_MARA FOR ALL ENTRIES IN T_MATERIALS WHERE

MATNR EQ I_MATERIALS-MATNR AND

MTART IN S_MTART.

ENDIF.

Note: Alwayz check whether the internal table is initial before using FOR ALL ENTRIES.This will help you escaping from bottleneck in most of the situations...

Cheers,

Abdul Hakim

Re: Help with INNER JOIN

RichHeilman — Thu, 27 Jul 2006 23:52:38 GMT

First make sure that you sort the T_MATERIALS2 internal table by MATNR and WERKS. Try this select statement.



check not t_materials2[] is initial.
sort t_materials2 ascending by matnr werks.

SELECT b~matnr b~mtvfp b~werks
       b~dismm b~beskz b~plifz b~disgr
       c~pstat c~mtart c~brgew c~normt z~mtpos
    INTO CORRESPONDING FIELDS OF TABLE t_materials
        FROM marc AS b 
           INNER JOIN mara AS c 
              ON a~matnr = c~matnr
           INNER JOIN mvke AS z
              ON a~matnr = b~matnr 
                 FOR ALL ENTRIES IN t_materials2
                    WHERE b~matnr EQ t_materials2-matnr
                    AND b~werks EQ t_materials2-werks
                    AND b~stawn IN s_stawn
                    AND b~disgr IN s_disgr
                    AND b~mmsta EQ '03'
                    AND c~mtart IN s_mtart
                    AND z~vkorg EQ p_vkorg
                    AND z~vtweg EQ p_vtweg
                    AND z~mtpos IN s_mtpos.

REgards,

Rich Heilman

Re: Help with INNER JOIN

Former Member — Thu, 27 Jul 2006 23:59:23 GMT

Hi Rich,

Thanks for the suggestions. I dint know that the order of joining the tables effect the performance.

I ll give it a try, and keep you posted.

Thanks

Brain

Re: Help with INNER JOIN

RichHeilman — Fri, 28 Jul 2006 00:03:42 GMT

Yes, it does, really I think that MARA should be first in line, you may want to try that too.

Reason for this is material is one record per material in MARA, MARC is many and MVKE is many. You should start the join from the least number of records to the most.

Regards,

Rich Heilman

Re: Help with INNER JOIN

former_member186741 — Fri, 28 Jul 2006 00:04:21 GMT

as Rich pointed out, the table should be sorted into matnr/werks sequence..i THINK MOVING mara HIGHER UP THE MATCHING LEVEL WILL IMPROVE THINGS:

SELECT a_{mtpos b}matnr b_{mtvfp b}werks

b_{dismm b}beskz b_{plifz b}disgr

c_{pstat c}mtart c_{brgew c}normt

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mvke AS a

<b>INNER JOIN mara AS c ON a_{matnr = c}matnr

INNER JOIN marc AS b

ON C_{matnr = b}matnr</b>

FOR ALL ENTRIES IN t_materials2

WHERE a~matnr EQ t_materials2-matnr

AND a~vkorg EQ p_vkorg

AND a~vtweg EQ p_vtweg

AND a~mtpos IN s_mtpos

<b>AND c~mtart IN s_mtart</b>

AND b~werks EQ t_materials2-werks

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND b~mmsta EQ '03'

Re: Help with INNER JOIN

former_member186741 — Fri, 28 Jul 2006 00:19:36 GMT

I would usually agree with Rich's analysis, i.e. make MARA the first port of call. But as you have only one value for vkorg and vtweg MVKE may actually be more efficient in this case so MVKE may be best this time. HAve a play around with the various options.

Re: Help with INNER JOIN

RichHeilman — Fri, 28 Jul 2006 00:26:42 GMT

I've been messing around with it for the past couple minutes, I'm seeing reasonable performance with this example program. Looking at 5000 records from the T_MATERIALS2 table, the other SELECT(inner join) is taking about 4 seconds. To me, this is reasonable. Of course this can be tweeked more.



report zrich_0001.

data: begin of t_materials occurs 0,
      matnr type marc-matnr,
      werks type marc-werks,
      mtvfp type marc-mtvfp,
      dismm type marc-dismm,
      beskz type marc-beskz,
      plifz type marc-plifz,
      disgr type marc-disgr,
      pstat type mara-pstat,
      mtart type mara-mtart,
      brgew type mara-brgew,
      normt type mara-normt,
      mtpos type mvke-mtpos,
      end of t_materials.

data: begin of t_materials2 occurs 0,
      matnr type marc-matnr,
      werks type marc-werks,
      end of t_materials2.


select matnr werks into table t_materials2
            from marc up to 5000 rows
                        where matnr > '000000000060001262'.


check not t_materials2[] is initial.
sort t_materials2 ascending by matnr werks.

select b~matnr b~mtvfp b~werks
       b~dismm b~beskz b~plifz b~disgr
       c~pstat c~mtart c~brgew c~normt z~mtpos
    into corresponding fields of table t_materials
        from mara as c
           inner join marc as b
              on b~matnr = c~matnr
           inner join mvke as z
              on b~matnr = z~matnr
                 for all entries in t_materials2
                    where b~matnr eq t_materials2-matnr
                    and b~werks eq t_materials2-werks.
*                    and b~stawn in s_stawn
*                    and b~disgr in s_disgr
*                    and b~mmsta eq '03'
*                    and c~mtart in s_mtart
*                    and z~vkorg eq p_vkorg
*                    and z~vtweg eq p_vtweg
*                    and z~mtpos in s_mtpos.


check sy-subrc  = 0.

Regards,

Rich Heilman

Re: Help with INNER JOIN

Former Member — Fri, 28 Jul 2006 00:32:18 GMT

Thanks a lot for all the pain...I ll also try and see all options and use the best one. I ll kepp you all posted.

Thanks

Brain.

Re: Help with INNER JOIN

Former Member — Mon, 31 Jul 2006 18:26:15 GMT

Hi Rich and Neil,

Just to let you know, i played around with the inner join and found out the code below to be the best based on some initial testing and SQL trace,

SELECT c_{pstat c}mtart c_{brgew c}normt

b_{matnr b}mtvfp b_{werks b}dismm

b_{beskz b}plifz b_{disgr z}mtpos

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mara AS c

INNER JOIN marc AS b

ON c_{matnr = b}matnr

INNER JOIN mvke AS z

ON c_{matnr = z}matnr

FOR ALL ENTRIES IN t_materials2

WHERE b~matnr EQ t_materials2-matnr

AND b~werks EQ t_materials2-werks

AND b~mmsta EQ '03'

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND c~mtart IN s_mtart

AND z~vkorg EQ p_vkorg

AND z~vtweg EQ p_vtweg

AND z~mtpos IN s_mtpos.

Thanks a lot for all the effort.

Brain

Re: Help with INNER JOIN

former_member186741 — Mon, 31 Jul 2006 23:17:35 GMT

Thanks for the points Brain. It's interesting that the where clause on the marc ahead of the mara has proved most efficient. I would have guessed that where clauses on mara first to reduce access to marc would have been best,

eg something like this:

WHERE c~matnr EQ t_materials2-matnr

AND c~mtart IN s_mtart

AND b~werks EQ t_materials2-werks

AND b~mmsta EQ '03'

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND z~vkorg EQ p_vkorg

etc

ps, my own pedantic preference is not to use 'as a' etc (unless table names are really long) as I always have difficulty translating back to the real table names. So I would code it:

SELECT mara_{pstat mara}mtart mara_{brgew mara}normt

marc_{matnr marc}mtvfp marc_{werks marc}dismm

marc_{beskz marc}plifz marc_{disgr mvke}mtpos

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mara

INNER JOIN marc

ON marc_{matnr = mara}matnr

INNER JOIN mvke

ON mvke_{matnr = mara}matnr

FOR ALL ENTRIES IN t_materials2

WHERE mara~matnr EQ t_materials2-matnr

AND mara~mtart IN s_mtart

AND marc~werks EQ t_materials2-werks

AND marc~mmsta EQ '03'

AND marc~stawn IN s_stawn

AND marc~disgr IN s_disgr

AND mvke~vkorg EQ p_vkorg

etc