Join optimization

[Former Member]

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.

[gouravkumar64]

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.

[RaymondGiuseppi]

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

[RaymondGiuseppi]

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

[gouravkumar64]

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.

[RaymondGiuseppi]

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