topic Re: Performance improvment for RESB table fetching in Application Development and Automation Discussions

Performance improvment for RESB table fetching

vijay_kumar134 — Fri, 22 Mar 2013 07:41:41 GMT

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.

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 07:47:11 GMT

Hi Vijay,

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

than it will work fine.

Thanks

Tarak

Re: Performance improvment for RESB table fetching

vijay_kumar134 — Fri, 22 Mar 2013 07:55:29 GMT

Hi Tarak,

Thanks for your response.

i tried that way too..

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 07:56:03 GMT

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

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 08:00:20 GMT

hi,

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.
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.

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 08:03:20 GMT

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

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 08:06:01 GMT

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.

Re: Performance improvment for RESB table fetching

vijay_kumar134 — Fri, 22 Mar 2013 09:44:48 GMT

Thanks for all.

Now am trying the possibilities what you have sugessted.

Let you know.

Re: Performance improvment for RESB table fetching

RaymondGiuseppi — Fri, 22 Mar 2013 10:15:59 GMT

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

Re: Performance improvment for RESB table fetching

Former Member — Fri, 22 Mar 2013 10:29:24 GMT

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......

Re: Performance improvment for RESB table fetching

guilherme_frisoni — Fri, 22 Mar 2013 11:35:40 GMT

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

Re: Performance improvment for RESB table fetching

vijay_kumar134 — Tue, 26 Mar 2013 14:33:30 GMT

Hi Guilherme,

Your sugession worked out.

Thanks you verymuch.