topic Re: Inner Join Performance issue in Application Development and Automation Discussions

Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 20:54:49 GMT

Hi experts,

I have the following select statement which has three levels of inner joins.

SELECT p_{plnty p}plnnr p_{plnkn m}plnal m_{werks m}matnr mr~meins

p_{vornr p}steus s_{datuv s}loekz INTO TABLE it_group

FROM ( ( plpo AS p

INNER JOIN plas AS s ON s_{plnty = p}plnty AND

s_{plnnr = p}plnnr AND s_{plnkn = p}plnkn )

INNER JOIN mapl AS m ON m_{plnty = s}plnty AND

m_{plnnr = s}plnnr AND m_{plnal = s}plnal )

INNER JOIN mara AS mr ON mr_{matnr = m}matnr

FOR ALL ENTRIES IN it_valid_opr

WHERE p~plnty = c_routing

AND p~plnnr = it_valid_opr-plnnr

AND s_{datuv <= p_valdt AND m}plnty =

c_routing AND m_{datuv <= p_valdt AND m}loekz <> c_x .

i have some 2500 entries in the internal table it_valid_opr. This query is taking very long time to get executed. Any anyone suggest an alternative for the above SELECT query.

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 21:15:46 GMT

You have one expression in both a JOIN condition and the WHERE. I'd remove it from the WHERE:

SELECT p~plnty p~plnnr p~plnkn m~plnal m~werks m~matnr mr~meins
       p~vornr p~steus s~datuv s~loekz
  INTO TABLE it_group
  FROM ( ( plpo AS p
    INNER JOIN plas AS s
      ON s~plnty = p~plnty AND
         s~plnnr = p~plnnr AND
         s~plnkn = p~plnkn )
    INNER JOIN mapl AS m
      ON m~plnty = s~plnty AND
         m~plnnr = s~plnnr AND
         m~plnal = s~plnal )
    INNER JOIN mara AS mr
      ON mr~matnr = m~matnr
  FOR ALL ENTRIES IN it_valid_opr
  WHERE p~plnty = c_routing
    AND p~plnnr = it_valid_opr-plnnr
    AND s~datuv <= p_valdt
*   AND m~plnty = c_routing              "<====
    AND m~datuv <= p_valdt
    AND m~loekz <> c_x .

Rob

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 21:29:29 GMT

Thanks for ur quick reply. But is this the only reason wat is causing the performance slow down?

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 21:32:18 GMT

Hi,

Types: begin of ty_plpo,
        plnty type plnty,
        plnnr type plnnr,
        plnkn type plnkn,
        vornr type vornr,
        steus type steus,
       end of ty_plpo,

       begin of ty_plas,
        plnty type plnty,
        plnnr type plnnr,
        plnal type plnal,
        datuv type datuv,
        loekz type loekz,        
       end of ty_plas,

       begin of ty_mapl,
        plnty type plnty,
        plnnr type plnnr,
        plnkn type plnkn,
        plnal type plnal,
        werks type werks_d,
        matnr type matnr,
       end of ty_mapl,
  
       begin of ty_mara,
        matnr type matnr,
        meins type meins,
       end of ty_mara.

Data: it_plpo type table of ty_plpo,
      it_mplo type table of ty_mplo,
      it_mapl type table of ty_mapl,
      it_mara type table of ty_mara.



if it_valid_opr[] is not intial.
 SELECT plnty plnnr plnkn vornr steus from plpo into table it_plpo for all entries in it_valid_opr
where plnty = c_routing
  and plnnr = it_valid_opr-plnnr.
 if sy-subrc = 0.
  select plnty plnnr plnal datuv loekz from plas into table it_plas for all entries in it_mplo
 where plnty = it_mplo-plnty
   and plnnr = it_mplo-plnnr
   and plnkn = it_mplo-plnkn
   and datuv LE p_valdt.
  if sy-subrc = 0.
   select plnty plnnr plnkn plnal werks matnr from mapl into table it_mapl for all entries in it_plas
     where plnty = it_plas-plnty
       and plnnr = it_plas-plnnr
       and plnkn = it_plas-plnal
       or  plnty = c_routing
       and datuv LE p_valdt.
   if sy-subrc = 0.
     delete it_mapl where loekz EQ c_x.
     select matnr meins from mara into table it_mara for all entries in it_mapl 
      where matnr = it_mapl-matnr.
   endif. 
  endif.
 endif.
endif.

Regards,

Satish

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 21:52:21 GMT

Performance can be bad for any number of reasons. The JOIN looks OK. How many records does it bring back?

Rob

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 22:00:21 GMT

Hi Rob,

It returns about 300,000 records....

Re: Inner Join Performance issue

Former Member — Fri, 07 Dec 2007 22:16:07 GMT

Well, that's probably it then. One of the first rules of performance tuning is to keep the selection set small. 300,000 records isn't huge, but it isn't small either. Coupled with the fact that you aren't using the entire key.

Rob