Performance improvment for RESB table fetching

[vijay_kumar134]

Hi,

The below Select query is taking more than 1 htr to run in production. Please help me for better way of performance tuning:

* Reading resb data
    clear git_ekpo1[].
    git_ekpo1[] = git_ekpo[].
    delete adjacent duplicates from git_ekpo1 comparing ebeln ebelp.

    if s_matnr is initial.
      select xloek                                      "#EC CI_NOFIRST
             matnr
             charg
             bdmng
             meins
             shkzg
             ebeln
             ebelp from  resb
                   into  table git_resb
                   where "matnr NE space   AND
                         werks eq p_werks and
                         xloek eq c_xloek.

      delete git_resb where matnr eq space.                

      sort git_ekpo by ebeln ebelp.
      loop at git_resb into gwa_resb.
        read table git_ekpo into gwa_ekpo with key ebeln = gwa_resb-ebeln
                                                   ebelp = gwa_resb-ebelp binary search.
        if sy-subrc ne 0.
          delete git_resb.
        endif.
      endloop.

      if not s_charg is initial.
        delete git_resb where charg not in s_charg.
      endif.

      delete git_resb where shkzg ne c_shkzg.

    else.
      select xloek
             matnr
             charg
             bdmng
             meins
             shkzg
             ebeln
             ebelp from  resb
                   into  table git_resb
                   where matnr in s_matnr and
                         werks eq p_werks and
                         xloek eq c_xloek.
      sort git_ekpo by ebeln ebelp.
      loop at git_resb into gwa_resb.
        read table git_ekpo into gwa_ekpo with key ebeln = gwa_resb-ebeln
                                                   ebelp = gwa_resb-ebelp binary search.
        if sy-subrc ne 0.
          delete git_resb.
        endif.
      endloop.

      if not s_charg is initial.
        delete git_resb where charg not in s_charg.
      endif.

      delete git_resb where shkzg ne c_shkzg.
    endif.

    sort git_resb by ebeln ebelp matnr charg ascending.
    delete git_resb where charg = ' '.
  endif.

Thanks.
Viji.

[vijay_kumar134]

Hi,

The below Select query is taking more than 1 htr to run in production. Please help me for better way of performance tuning:

* Reading resb data
    clear git_ekpo1[].
    git_ekpo1[] = git_ekpo[].
    delete adjacent duplicates from git_ekpo1 comparing ebeln ebelp.

    if s_matnr is initial.
      select xloek                                      "#EC CI_NOFIRST
             matnr
             charg
             bdmng
             meins
             shkzg
             ebeln
             ebelp from  resb
                   into  table git_resb
                   where "matnr NE space   AND
                         werks eq p_werks and
                         xloek eq c_xloek.

      delete git_resb where matnr eq space.                

      sort git_ekpo by ebeln ebelp.
      loop at git_resb into gwa_resb.
        read table git_ekpo into gwa_ekpo with key ebeln = gwa_resb-ebeln
                                                   ebelp = gwa_resb-ebelp binary search.
        if sy-subrc ne 0.
          delete git_resb.
        endif.
      endloop.

      if not s_charg is initial.
        delete git_resb where charg not in s_charg.
      endif.

      delete git_resb where shkzg ne c_shkzg.

    else.
      select xloek
             matnr
             charg
             bdmng
             meins
             shkzg
             ebeln
             ebelp from  resb
                   into  table git_resb
                   where matnr in s_matnr and
                         werks eq p_werks and
                         xloek eq c_xloek.
      sort git_ekpo by ebeln ebelp.
      loop at git_resb into gwa_resb.
        read table git_ekpo into gwa_ekpo with key ebeln = gwa_resb-ebeln
                                                   ebelp = gwa_resb-ebelp binary search.
        if sy-subrc ne 0.
          delete git_resb.
        endif.
      endloop.

      if not s_charg is initial.
        delete git_resb where charg not in s_charg.
      endif.

      delete git_resb where shkzg ne c_shkzg.
    endif.

    sort git_resb by ebeln ebelp matnr charg ascending.
    delete git_resb where charg = ' '.
  endif.

Thanks.
Viji.

[Former Member]

Hi Vijay,

If possible try to create one Secondery Index for the Table for Field WERKS.

than it will work fine.

Thanks

Tarak

[vijay_kumar134]

Hi Tarak,

Thanks for your response.

i tried that way too..

[Former Member]

hi,

1. Dont use delete inside the loop.. it ll reduce your performance.. instead set a flag to field or variable and delete it outside the varaiable.
2. Specify all key fields in where condition.. in a rare case create secondary index in se11 for that particular table and use it in program via statement...

SELECT * FROM <Table>

  %_HINTS ORACLE 'INDEX("<Table Name>" "<Table Name>~<Index Id>")'

  3.    Better use single select query for sinle table.. donn use more than one select query to fetch data from a same table..

hope it helps,

Mathan R.

[Former Member]

Hi Vijay,

After create the INDEX it will take more tha 1 hours .....

Do one thing and try it.

PLease give all Primary Key in the time select data from table.

take all the PRIMARY KEY into ur Internal table and select all those Primary Key.

Try it.and let me know pls.

Thanks

Tarak

[Former Member]

Use the below code.

Select RSNUM

               RSPOS

               RSART

               matnr

             charg

             bdmng

             meins

             shkzg

             ebeln

ebelp from  resb

                   into  table git_resb

                   where matnr in s_matnr and

                         werks eq p_werks and

                         xloek eq c_xloek.

[Former Member]

Hi ,

in your case you can create a secondary index.

About index you can see this link

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb20446011d189700000e8322d00/content.htm

Regards

Ivan

[vijay_kumar134]

Thanks for all.

Now am trying the possibilities what you have sugessted.

Let you know.

[RaymondGiuseppi]

First analyze execution with SAT or SE30 and ST05 to identify step(s) which cause performance problems (SQL statements or Internal table management)

If you want an efficient index I would suggest keys MANDT, WERKS, XLOEK and MATNR for RESB.

Add as much selections to the initial select, e.g. IN s_charg, EQ c_shkzg

A last trick could be usage of a subselect with your initial selection on ekko/ekpo

AND EXISTS ( SELECT * FROM ekpo 
             WHERE ebeln = resb~ebeln AND ebelp = resb~ebelp AND ... )  

Regards,

Raymond

[Former Member]

Hi VijayKumar,

create secondary index for the table,

write delete statement in outside the loop, everyloop ittration program is going to hit the database that is also one of the performance issue. Write all delete statements in outside the loop.

Thanks

Krish......

[guilherme_frisoni]

Hi Viji,

create a secondary index in RESB with EBELN and EBELP and use FOR ALL ENTRIES.

Your code could be just like that:

select xloek                                      "#EC CI_NOFIRST

          matnr

          charg

          bdmng

          meins

          shkzg

          ebeln

          ebelp

from  resb

into  table git_resb

for all entries in git_ekpo1

  where ebeln eq git_ekpo1-ebeln and

           ebelp eq git_ekpo-ebelp and

           matnr IN s_matnr  AND

           werks eq p_werks and

           xloek eq c_xloek.

DELETE git_resb qher matnr is initial.

Regards,

Frisoni

[vijay_kumar134]

Hi Guilherme,

Your sugession worked out.

Thanks you verymuch.