Re: What's the fastest way to UPDATE?
This was an interesting exercise and I wanted to try a
few things out. Sorry its taken so long to respond.
Will, what did you end up doing?
> From: Will Hartung - Master Rallyeist <villy@uunet.uu.net>
> We've got a process that basically needs to update every row in a 2
> million row table. The row size is ~260 bytes, so it's not huge.
>
> Basically, when we're dealing with 2 millions rows, small gains in
> performance can have a measureable effect. Heck, a gain of 1
> millisecond nets about 40 minutes :-). So, even small gains can help.
>
<< Proposed solutions and discussion deleted to save space >>
To try a couple of things out I took a 40,000+ record zip code table,
added a price field and set the price to the number in the zip code.
I then tried several ways of updating the price by 110%. Here are the
results of the two most interesting tests in 4GL.
Test 1: This is the Brute forse method like your option 5 and
was the fastest I could come up with. As others
suggested, you would want to turn logging off, and delete
the begin and commit work.
## Test 1 4GL Code
database zip
main
begin work
update zip set ( price ) = (price *1.1 ) where 1=1
commit work
display "Update complete "
display ""
end main
time: 1:25.12
---------------------------------
Test 2: This uses two cursors one to select all rows
and the other to update each row. As each updated
row is committed, if it failed 1/2 through, only the
current row would roll back and you could restart
from the rowid where it failed. Also as it locks
one row a a time, it could be done while others
were using the data. The Problem is this method
takes Six times longer. I had hoped the difference
between the two tests would smaller and therefore
the restart capability of the secound test would
make it a better choice.
## Test 2 4GL Code
database zip
globals
define price like zip.price,
new_price like zip.price,
rowno integer
end globals
main
prepare pc_select_all from " select rowid from zip "
declare c_select_all cursor with hold for pc_select_all
prepare pc_lock from "select price from zip where rowid = ? \\
for update of price"
declare c_lock cursor for pc_lock
prepare pc_upd from "update zip set ( price ) = ( ? ) \\
where current of c_lock "
foreach c_select_all into rowno, price
begin work ## uncomment if logging is on
open c_lock using rowno
fetch c_lock into price
let new_price = price * 1.1
execute pc_upd using new_price
commit work ## uncomment if logging is on
end foreach
display "Update complete "
display ""
end main
Time: 6:37.00
---------------------------------
I will keep my test database around for a few days if anyone
as any other suggestions.
Regards - Lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
# Visit us at the Informix Worldwide User Conference, Booth 342 #
#############################################################################