topic Re: Improve performance of a SQL request in Application Development and Automation Discussions

Improve performance of a SQL request

Former Member — Tue, 06 Nov 2007 16:47:58 GMT

Hi.

I have a sql request with multiple joins in it and when i launch my program i have some performance problems.

I think it's because of the multiple Joins.

How can i improve it ?

Do i need to split it in different smaller requests ?

Thanks for your help and tips.


    SELECT e~ebeln
           e~ebelp
           e~werks
           e~matkl
           e~mtart
           k~lifnr
           k~bstyp
           k~ekgrp
           k~bsart
           k~zzreldt
           t~slfdt
           b~budat
           e~banfn
           e~bnfpo
    FROM ekpo AS e JOIN ekko AS k ON e~ebeln = k~ebeln
                   JOIN eket AS t ON e~ebeln = t~ebeln
                                  AND e~ebelp = t~ebelp
                   JOIN ekbe AS b ON e~ebeln = b~ebeln
                                  AND e~ebelp = b~ebelp
                   JOIN mseg AS m ON e~ebeln = m~ebeln
                                  AND e~ebelp = m~ebelp
    INTO CORRESPONDING FIELDS OF TABLE t_commande
    WHERE k~bstyp IN so_bstyp
    AND   k~lifnr IN so_lifnr
    AND   e~werks IN so_werks
    AND   e~matkl IN so_matkl
    AND   e~mtart IN so_mtart
    AND   k~ekgrp IN so_ekgrp
    AND   k~bsart IN so_bsart
    AND   b~budat IN so_date
    AND   t~etenr EQ '1'
* Ajout des restriction sur code mouvement et stock bloqué
    AND   m~bwart EQ '101'
    AND   m~insmk EQ '3'
* On ne prend que le commandes ayant une référence à une DA
    AND   e~banfn NE ''.

Re: Improve performance of a SQL request

Former Member — Tue, 06 Nov 2007 17:02:04 GMT

You can check before the SELECT to make sure that the select-options against key fields are not empty. For example:

CHECK NOT so_lifnr IS INITIAL.

SELECT ...

Rob

Re: Improve performance of a SQL request

Former Member — Tue, 06 Nov 2007 19:45:43 GMT

Hi,

Do like this

declare independent internal tables for each tables

select ebeln ebelp werks matkl mtart from ekpo into table git_ekpo
 where werks in so_werks
   and matkl in so_matkl
   and mtart in so_mtart.
if sy-subrc = 0.
 if not git_ekpo[] is initial.
  select lifnr bstyp ekgrp bsart zzreldt from ekko into table git_ekko
    for all entries in git_ekpo
      where ebeln = git_ekpo-ebeln
        and bstyp in so_bstyp
        and lifnr in so_lifnr
        and ekgrp in so_ekgrp
        and bsart in so_bsart.
  if sy-subrc = 0.
   delete git_ekpo where banfn = ' '.
   select ebeln ebelp slfdt from eket into table git_eket
    for all entries in git_ekpo
     where ebeln = git_ekpo-ebeln
       and ebelp = git_ekpo-ebelp
       and etenr = '1'.
   if sy-subrc = 0.
     select ebeln ebelp budat banfn bnfpo from ekbe into table git_ekbe
      for all entries in git_ekpo
       where ebeln = git_ekpo-ebeln
         and ebelp = git_ekpo-ebelp
         and budat in so_budat.
     if sy-subrc = 0.
      select <fields> from mseg into table git_mseg
       for all entries in git_ekpo
        where ebeln = git_ekpo-ebeln
          and ebelp = git_ekpo-ebelp
          and bwart = '101'
          and insmk = '3'.
     endif.
   endif. 
  endif.
 endif.
endif.

once you get the whole data merge into a single internal table with all the fields.

Hope your problem will get resolve.

<b>Reward points if it helps</b>

Satish

Re: Improve performance of a SQL request

Former Member — Fri, 09 Nov 2007 10:02:51 GMT

Thanks for your suggestion.

It partially solved my problem.

Re: Improve performance of a SQL request

Former Member — Fri, 09 Nov 2007 10:07:18 GMT

Still what is pending to resolve your whole problem?