topic Re: Custom table update is creating performance issue in Application Development and Automation Discussions

Custom table update is creating performance issue

Former Member — Fri, 30 Apr 2010 06:33:32 GMT

Hi Experts,

Please guide me.

These logs were generated on mentioned days.

On 23rd April:

09:45:36 @0S@ Finishing numbr BSEG recs is ... 3,562

11:04:32 @08@ 3,562 Transmitted Items stored to ZSAPtoTOSlog

On 27th April:

10:30:29 @0S@ Finishing numbr BSEG recs is ... 14,616

13:36:51 @08@ 14,616 Transmitted Items stored to ZSAPtoTOSlog

As per the log for 3,562 records 1hr 15 minutes is taken but for 14,616 records 3 hours.

ZSAPtoTOSlog is a custom table.

This code is taking a lot time......

FORM f_record_into_log.

New form for 112443

Record transmission into LOG database

Update with XBLNR and Todays date

CLEAR wa_bseg.

LOOP AT i_bseg INTO wa_bseg.

At new Company code, doc. number and Fiscal year.

AT NEW gjahr.

CLEAR wa_bkpf.

CLEAR wa_zsaptotoslog_ins.

Read table I_BKPF with key company,number and year.

READ TABLE i_bkpf INTO wa_bkpf WITH KEY bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

If the record is found

IF sy-subrc EQ 0.

wa_zsaptotoslog_ins-xblnr = wa_bkpf-xblnr.

ENDIF.

ENDAT.

wa_zsaptotoslog_ins-mandt = sy-mandt.

wa_zsaptotoslog_ins-bukrs = wa_bseg-bukrs.

wa_zsaptotoslog_ins-belnr = wa_bseg-belnr.

wa_zsaptotoslog_ins-gjahr = wa_bseg-gjahr.

wa_zsaptotoslog_ins-buzei = wa_bseg-buzei.

wa_zsaptotoslog_ins-augcp = wa_bseg-augcp.

wa_zsaptotoslog_ins-augbl = wa_bseg-augbl.

wa_zsaptotoslog_ins-wrbtr = wa_bseg-wrbtr.

wa_zsaptotoslog_ins-intdate = v_today.

Add entry into internal table for Log

APPEND wa_zsaptotoslog_ins TO i_zsaptotoslog_ins.

ENDLOOP.

MODIFY zsaptotoslog FROM TABLE i_zsaptotoslog_ins.

commit.

IF sy-subrc = 0.

MESSAGE ID 'ZFI_SDF' TYPE 'I' NUMBER 065 WITH icon_green_light

sy-dbcnt 'Transmitted Items stored to ZSAPtoTOSlog'.

ELSE.

MESSAGE ID 'ZFI_SDF' TYPE 'I' NUMBER 065 WITH icon_information

sy-dbcnt 'Transmitted Items stored to ZSAPtoTOSlog'.

ENDIF.

ENDFORM. " f_record_into_log

Please guide me to tune this as it is causing performance issue.

Regards,

Nutan

Re: Custom table update is creating performance issue

Former Member — Fri, 30 Apr 2010 07:28:40 GMT

I think there is nothing new, you have the usual problems:


LOOP AT i_bseg INTO wa_bseg.
* At new Company code, doc. number and Fiscal year.
AT NEW gjahr.
CLEAR wa_bkpf.
CLEAR wa_zsaptotoslog_ins.
>>>> * Read table I_BKPF with key company,number and year.
>>>> READ TABLE i_bkpf INTO wa_bkpf WITH KEY bukrs = wa_bseg-bukrs
>>>>       belnr = wa_bseg-belnr
>>>>       gjahr = wa_bseg-gjahr.
* the READ MUST use a BINARY SEARCH or better a sorted table !!!

* If the record is found

....

*Don't use the MODIFY, you must figure out, which lines are in the table (UPDATE) and which ones are
* new, then use the ARRAY changes
MODIFY zsaptotoslog FROM TABLE i_zsaptotoslog_ins.

Siegfried

Re: Custom table update is creating performance issue

Former Member — Fri, 30 Apr 2010 12:52:04 GMT

Hi Siegfried,

READ TABLE i_bkpf INTO wa_bkpf WITH KEY bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.</div></div></div></div>

the READ MUST use a BINARY SEARCH or better a sorted table !!!

As all the fields are primary keys, so i think it should not create any issues.

Much problem is with Modify statement.

*Don't use the MODIFY, you must figure out, which lines are in the table (UPDATE) and which ones are

new, then use the ARRAY changes

MODIFY zsaptotoslog FROM TABLE i_zsaptotoslog_ins.

As per your suggestion. Please explain it more, because I am very new in ABAP, so unable to make it out properly.

Regards,

Nutan

Re: Custom table update is creating performance issue

Rui_Dantas — Fri, 30 Apr 2010 14:19:52 GMT


> Siegfried: * the READ MUST use a BINARY SEARCH or better a sorted table !!!
>
> As all the fields are primary keys, so i think it should not create any issues. 
> Nutan

Remember you are talking about an INTERNAL table here. Have you declared it as SORTED table? If not you'll have the performance issues Siegfried is describing. Please post here how you declared i_bkpf.


> As all the fields are primary keys, so i think it should not create any issues. 
> Much problem is with Modify statement.
> Nutan

From my experience much time is spent optimizing because one thinks and assumes the problem is somewhere and often it's somewhere else. The rule is don't think: measure it (with SE30, for example) and then you can be sure and optimize what really needs to be optimized.

Rui Dantas

Re: Custom table update is creating performance issue

Former Member — Mon, 03 May 2010 02:22:06 GMT

Hi Nutan

In general, if you have a large number of entries in internal tables (hundred is a large number in this case) and you want to avoid performance issues, do not use internal table of type STANDARD.

Therefore we suggest to use sorted tables:


data: i_bkpf type sorted table of bkpf
             with unique key bukrs belnr gjahr,
      i_bseg type sorted table of bseg
             with unique key bukrs belnr gjahr buzei.

field-symbols: <bkpf> type bkfp,
               <bseg> type bseg.

Additionally, do not use LOOP itab INTO or READ TABLE itab INTO, but ASSIGNING field symbol (pointer).


loop at i_bseg assigning <bseg>.
...
  read table i_bkpf assigning <bkpf>
       with table key bukrs = <bseg>-bukrs
                      belnr = <bseg>-belnr
                      gjahr = <bseg>-gjahr.

And finally, do not use MODIFY, but INSERT, if you know the table entries do not exist.


  insert zsaptotoslog FROM TABLE i_zsaptotoslog_ins.

Please let us know by how much your runtime improved with those changes

Yep

Jürgen

Edited by: sattlerj on May 3, 2010 2:23 PM

Edited by: sattlerj on May 3, 2010 2:24 PM

Re: Custom table update is creating performance issue

Former Member — Tue, 19 Oct 2010 12:28:45 GMT

changed the modify statement to insert. It worked fine.