Re: What's the fastest way to UPDATE?

In article <2t8len$2bk@emory.mathcs.emory.edu>, villy@uunet.uu.net (Will Hartung - Master Rallyeist) says: > >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. ... lots of code omitted .. My observations: (1) If you're spending that much time on a job, write it in ESQL/C, not 4GL. If you are passing every row through the application program, C will be faster. (2) One scenario mentioned processing rows in index order. DON'T do it that way. It will take forever. Let the engine do a table scan in row order. That way, each page will be read and written only once. (3) The most efficient way is "UPDATE tbl SET x = y". But, that will lock the table until the update is complete. It will also cause long transactions in a logged database. (4) The idea of "UPDATE tbl SET x = y WHERE rowid BETWEEN a AND B will work, but you will still lock parts of the table during each statement. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ Jim Burks jburks@promus.com ~ ~ Database Administrator / Systems Engineer ~ ~ The Promus Companies, Inc. Memphis, TN USA ~ ~ http://stargate.promus.com/public/jbb.html ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~