Re: What's the fastest way to UPDATE?

Will Hartung - Master Rallyeist (villy@uunet.uu.net) wrote: : Hi all! : 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. : So, here are the various possibilities that I'd like to present and : see what y'all think. : 4) LET sql_stmt = "UPDATE bigtable SET (this, that, other) = (?, ?, ?) ", : "WHERE rowid = ?" : PREPARE upd_stm FROM sql_stmt : DECLARE f_curs CURSOR FOR SELECT rowid, this, that, other FROM bigtable : FOREACH f_curs INTO r_rowid, l_this, l_that, l_other FROM bigtable : let l_other = l_this : let l_this = l_that : let l_that = 0 : EXECUTE upd_stm USING l_this, l_that, l_other, r_rowid : END FOREACH : 5) UPDATE bigtable SET (this, that, other) = (that, 0, this) : WHERE 1=1 : Ok, no syntax kibitzing, this is conceptual and you should get the : idea. <clip 3 million lines> Back up your database, turn logging off, lock the table and use option 4 or 5. Backing up your database has obvious benefit, turning off logging will eliminate log issues, locking the table will eliminate lock overflow issues. If the update will modify all gazillion rows with the same fixed data, option 5 is fine. If what gets updated is variable, option 4 is best. If you had started the update, you'd be done by now! :-) DCT -- @ David Tomlinson @ @ dtomlnsn@netcom.com @ @ "By day fantastic birds flew through the petrified forest, and @ @ jeweled crocodiles glittered like heraldic salamanders on the @ @ banks of the crystalline river. By night the illuminated man @ @ raced among the trees, his arms like golden cartwheels, his @ @ head like a spectral crown...." @