Update - statement performance Measure

[Former Member]

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

or

update ztable from table <itab>.

commit work.

Kindly guide me.

Thanks in advance

[Former Member]

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

[Former Member]

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

[Former Member]

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é

[Former Member]

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.

[Former Member]

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

yk

[Former Member]

Hope it help you.