What's the fastest way to UPDATE?

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. 1) DECLARE f_curs CURSOR FOR SELECT rowid,* FROM bigtable FOREACH f_curs INTO r_rowid,rec.* # Fiddle with data UPDATE bigtable SET * = rec.* WHERE rowid=r_rowid END FOREACH 2) DECLARE f_curs CURSOR FOR SELECT * FROM bigtable FOR UPDATE FOREACH f_curs INTO r_rowid,rec.* # Fiddle with data UPDATE bigtable SET * = rec.* WHERE CURRENT OF f_curs END FOREACH 3) 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 UPDATE bigtable SET (this, that, other) = (l_this, l_that, l_other) WHERE rowid = r_rowid END FOREACH 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. Anyway, Method 1 is about as brute force as you can get and will certainly do the job. Problems, IMHO, are: Continually processing the UPDATE statement. Moves the entire record back and forth from the 4GL. I see the same problems with Method 2 (I don't really think there is much of a difference between the two save that Method 2 should always work, even in 6/7.0 whereas Method 1 may not). Of course, with Method 2 I must be in a transaction, so I guess it would be a FOR UPDATE WITH HOLD cursor ( can I do a FOR UPDATE WITH HOLD cursor??). Nobody wants a transaction that 2 Millions rows long. Nor do they want 2 million locks. It's faster to TBUNLOAD the table to tape and restore it if the thing explodes than wait for thing to rollback. It's just to nasty on the logs. So, I guess a WITH HOLD cursor with some COMMIT WORKs sprinkled in the main loop could clear that up. Method 3 is a little different in that it doesn't ask for the entire row of data and then update the entire row of data. Since there isn't any real need to get the entire row, the other methods are a little wasteful. But I have to question a little bit because now rather than moving one continuous chunk of memory from the data buffers into the application and sending it down the pipe, it has to pick and choose it's fields. So, any gain you may get from having a smaller amount of data going down the pipe is lost by the logic picking it out. Same argument for sending those specific columns back into the engine. I dunno about that one. If the engine move column by column anyway (whether you asked for 1 column or all of them), then I'm totally blowing smoke and there will be zero benefit from asking for the whole row. Internally, the engine has to move the entire row anyways, and I believe the entire row gets stuck into the log no matter what also. So, why not move the entire thing and get rid of the extra loop overhead of picking out specific columns. Method 4, to me, seems as if it's about as efficient as it's going to get. We get rid of the UPDATE re-processing (I hope), and we only move the minimal amount of data. However, it seems to me that the implied commit of each UPDATE (since we're not in a transaction) would add up over the life of the process. A transaction commit is a pretty atomic process isn't it? It's the ROLLBACK that takes all of the time. It seems that a COMMIT just sets the "Yup! We're Done" flag and moves on. The COMMIT doesn't go and flag every record in the logical log saying that it's not needed anymore does it? I think it just clears out a higher level structure that the log records are associated with. So, if the COMMIT is a basically constant process, regardless of the amount of data in the transaction (or only relative to the tables involved and not the rows), then it would be better to commit every N thousand rows than on every row, eh? Hmmm... Method 5, if possible for the application, HAS to be the most efficient, at least from a code point of view. It's only optimized once and all of the data movement takes place within the engine so nothing is being communicated back to the host program, so no time is lost there. The problem is, again, with the size of the transaction. I can lock the table to eliminate the lock issue, but if the system get 9 hours into a 10 hour update, then it'll take a nice, slow 9 hours to roll it all back because it didn't quite make it. That's bad enough when it's just this process, and even worse if the engine crashed and EVERYONE is waiting for it to rollback before they can even log in. I guess we could break it up into pieces, but then we have the seek overhead of coming off of an index for whatever criteria I break this thing up in. I imagine that would make the other methods seem quick! I guess I could select MAX(rowid) from the table, and create a set of statements with "WHERE rowid BETWEEN 1 AND 40000; WHERE rowid BETWEEN 40001 AND 80000;" etc. Then I wouldn't have to use an index. The other problem is that we are taking advantage of the reading of the data to update another summary table. But maybe the gain would make it worth a second trip through the data (boy, that doesn't make a lot of sense now does it??). This all seems like splitting hairs (loop overhead???), but like I said in the beginning, the smallest saving can yield not only measurable, but NOTICABLE results (I mean a single hair is measurable, but rarely noticable -- unless it's in your soup of something...). Well, after dragging all of you folks along the "Informix Express" train of thought, I was hoping some may have comments and hilights to the ..umm.. trip. Thanx, Will (villy@collinscomp.com)