topic Re: Join optimization in Application Development and Automation Discussions

Join optimization

Former Member — Tue, 09 Oct 2012 11:17:21 GMT

Hi Friends -

There is a programe where in one select query it pull all the data of material document from MKPF for the provided plant as input., but it giving dump saying that " Time limit exceed ", Can we optimize the program so the user will not experience such type of dump in future. Its working fine in background but taking time to execute.

* Material Documents
perform show_progress using text-p03.
select h~mblnr
          h~mjahr
          h~budat
          h~oib_bltime
          h~cpudt
          h~cputm
          h~xblnr
          i~zeile
          i~bwart
          i~xauto
          i~matnr
          i~werks
          i~lgort
          i~charg
          i~shkzg
          i~sjahr
          i~smbln
          i~smblp
          i~ummat
          i~umwrk
          i~umlgo
          i~umcha
          i~grund
          into corresponding fields of table it_mmproc
          from mkpf as h inner join mseg as i
          on h~mblnr = i~mblnr and h~mjahr = i~mjahr
          where h~budat ge p_fdate
            and h~xblnr ne ' '
            and ( i~werks in s_plant or i~umwrk in s_plant )
            and ( i~lgort in r_lgort or i~umlgo in r_lgort )
*            and i~grund eq c_false
            and i~grund in r_grund
            and ( ( ( i~matnr eq w_plntcontrol-gen_crude and i~charg eq w_plntcontrol-gen_batch or
*                      i~ummat eq w_plntcontrol-gen_crude and i~umcha eq w_plntcontrol-gen_batch ) and bwart in r_bwart )

i~ummat eq w_plntcontrol-gen_crude and i~umcha eq w_plntcontrol-gen_batch or

i~matnr eq w_plntcontrol-gen_spike and i~charg eq w_plntcontrol-gen_batch or

i~ummat eq w_plntcontrol-gen_spike and i~umcha eq w_plntcontrol-gen_batch )

                      and bwart in r_bwart )                "+mod-003
* This will be taken out when the old material documents are no longer relevant for CC
* The new design post only generic crude!!!
            or
                ( ( i~matnr ne w_plntcontrol-gen_crude or i~charg ne w_plntcontrol-gen_batch ) and
                  ( i~matnr ne w_plntcontrol-gen_spike or i~charg ne w_plntcontrol-gen_batch ) and

( i~ummat ne w_plntcontrol-gen_spike or i~charg ne w_plntcontrol-gen_batch ) and

( i~ummat ne w_plntcontrol-gen_crude or i~umcha ne w_plntcontrol-gen_batch ) and bwart in r_bwart2 ) ).
*
commit work.

Re: Join optimization

gouravkumar64 — Tue, 09 Oct 2012 11:26:58 GMT

Hi,

Try to avoid

Join ,

Into corresponding fields of table .

try like this

select

mjahr

budat

oib_bltime

cpudt

cputm

xblnr

...........

from mkpf

into table it_mkpf

where.................

then

select

zeile

bwart

xauto

matnr

werks

lgort

into table it_mseg

for all entries in it_mkpf

where...............

select master table as per requirement

Hope Dump will Gone........

Thanks

Gourav.

Re: Join optimization

RaymondGiuseppi — Wed, 10 Oct 2012 12:50:47 GMT

Are you sure that a FOR ALL ENTRIES will reduce extraction duration, did you test on your system, I have some doubt on performance if p_fdate is not recent ?

Regards,

Raymond

Re: Join optimization

RaymondGiuseppi — Wed, 10 Oct 2012 13:07:51 GMT

Take a look at Note 1516684 - MKPF fields added to MSEG - Performance optimization

This note add some MKPF fields to MSEG and can help improve performance. You could, for example, use a MSEG JOIN MKPF and put the budat selection on mseg (i~budat_mkpf ge p_fdate)

Nevertheless, before implementing this note, perform first a SQL trace to analyze the access plan of SQL optimize.

Regards,

Raymond

Re: Join optimization

gouravkumar64 — Wed, 10 Oct 2012 13:20:30 GMT

Hi Raymond,

I did not see her full query.

At a glance just see the two statements like JOIN & INTO CORRESPONDING........

In our implementation project INNER JOIN

OCCURS 0 &

That statements taking too much time.

But sometimes There is some Feasible join condition with from proper table

provides BEST SOLUTION.

It is best for this condition .

Thanks For Sharing this NOTES.

@meeta ,please follow that note.

Regards

Gourav.

Re: Join optimization

RaymondGiuseppi — Wed, 10 Oct 2012 13:25:21 GMT

I enjoyed this note, it is worth the time of execution of the initial report, many MM transactions can be optimized. (Like MB51 or MB5B, look at end of note, of course adapt your own z-reports)

Regards,

Raymond