topic Re: Update - statement performance Measure in Application Development and Automation Discussions

Update - statement performance Measure

Former Member — Mon, 27 Jul 2009 07:07:51 GMT

Hi Experts,

I need to update a Ztable consisting of 10000 records.

which statement is better,

Loop at itab.

update ztable

set <Exp1>

<Exp2>.

Endloop.

commit work

update ztable from table <itab>.

commit work.

Kindly guide me.

Thanks in advance

Re: Update - statement performance Measure

Former Member — Mon, 27 Jul 2009 07:17:10 GMT

Hi ,

Both are good in some cases.



"  the below syantax acts as..if one record fails also other record will update..
   Loop at itab.
    update ztable  
                set <Exp1>
                        <Exp2>.
     Endloop.
    commit work


"below statement acts as,if one record fails in the middle other records also fails after the middle
  update ztable from table <itab>.
  commit work.

Prabhudas

Re: Update - statement performance Measure

Former Member — Mon, 27 Jul 2009 09:15:16 GMT

performancewise the lower is much better it uses array processing.

In both cases you must take care, that the updates are actually possible, i.e. records exist.

If this is not fulfilled then preprocessing could be a good idea for 10.000 rows.

Siegfried

Re: Update - statement performance Measure

Former Member — Mon, 31 Aug 2009 20:40:33 GMT

Hi priyadarshini lingaiah,

For the number of records will be updated, i recommend using:

data: lv_count type i.

Loop at itab.

update ztable

set .

add 1 to lv_count.

if lv_count = 1000.

commit work.

clear lv_count.

endif.

Endloop.

And commit every 1000 records to release the memory buffer the BD.

Hope this information is help to you.

Regards,

José

Re: Update - statement performance Measure

Former Member — Tue, 01 Sep 2009 03:23:44 GMT

Hi,

You use Field Symbols to process the loop for updating the database. This will speed up the process a lot more than normal procedure.

For the latter part of the code, you can use the Array operation for Database. The array operation is a much better approach and will enhance the performance of the code. But again the drawback is that if one of the Update fails, then automatically the rest will also fail.

Hope this helps.

Thanks,

Samantak.

Re: Update - statement performance Measure

Former Member — Mon, 07 Sep 2009 13:28:47 GMT

Hi,

the latter is the best for

performance (minimal use of database resources and actions)
but more important aligns with ACID properties of a database transaction:

remember: ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. An example of a transaction is a transfer of funds from one bank account to another, even though it might consist of multiple individual operations (such as debiting one account and crediting another).

This is not possible with the LOOP ... UPDATE ...COMMIT ... ENDLOOP approach.

The COMMIT breaks the atomicity of the business task.

If you can do a database operation in one statement, do it in 1 statement!

bye

Re: Update - statement performance Measure

Former Member — Tue, 08 Sep 2009 04:21:14 GMT

Hope it help you.