Updating millions of rows from SQL Server
So, you have a large table where you need to update every row, using some condition.
If you run a:
UPDATE SOMETABLE SET NAME = 'FRED' WHERE "SOME_CONDITION"
...you'll be waiting a long time. The table will be locked, the transaction space will grow and grow, and you won't know if it will ever finish until it does... or it doesn't.
One "trick" that people talk about is turning off indexes, so they don't have to be updated. But while that's effective for inserts, it struggles in this situation: because you end up having to do table scans to find which rows to update. So don't turn off indexes: double-down on indexes. Make sure the query you're using is able to efficiently find the records it wants to update.
Some people say: "select the table into a new, transformed table, then drop the old table and rename the new." That will double your space requirements and I think it can eradicate your statistics too, so I don't really go in for that.
What I like is the little loopy methods with
update top(n), similar (almost identical, natch) to the technique I use for Deleting millions of rows from SQL Server.
This example uses
Update top(@batchsize) ... in a
while loop to do the needful. It means you'll get a sense of how long it will take... and if it gets interrupted it can resume.
DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500), @STARTALL DATETIME, @STARTBATCH DATETIME, @ENDBATCH DATETIME SET DEADLOCK_PRIORITY LOW; SET @BATCHSIZE = 4000; SET @WAITFORVAL = '00:00:10' -- Delay between updates **** UPDATE THIS **** SET @MAXRUNTIME = '08:00:00' -- 8AM SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME SET @ITERATION = 0 -- LEAVE THIS SET @TOTALROWS = 0 -- LEAVE THIS SET @STARTALL = GetDate() WHILE @BATCHSIZE>0 BEGIN -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME... IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1 BEGIN RETURN END SET @STARTBATCH = GetDate() /* vv You need to update this! ***** */ UPDATE TOP(@BATCHSIZE) SET Column1 = 'Value' FROM SOMETABLE WHERE SOMECONDITION /* ^^ You need to update that ***** */ SET @BATCHSIZE=@@ROWCOUNT SET @ENDBATCH = GETDATE() SET @ITERATION=@ITERATION+1 SET @TOTALROWS=@TOTALROWS+@BATCHSIZE SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) RAISERROR (@MSG, 0, 1) WITH NOWAIT SET @MSG = 'Batch duration: ' + Cast(DATEDIFF(s, @STARTBATCH, @ENDBATCH) as varchar) + ' seconds. Batch Speed ' + CAST(CAST(@BATCHSIZE as float)/(DATEDIFF(s, @STARTBATCH, @ENDBATCH)) AS VARCHAR) + ' rows per seconds. Subtotal elapsed: ' + CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) + ' seconds. Cumulative Speed ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) + ' rows per second' RAISERROR (@MSG, 0, 1) WITH NOWAIT WAITFOR DELAY @WAITFORVAL -- ** DECIDE IF YOU WANT THIS BREATHING-ROOM DELAY ON EACH LOOP.... END SET @MSG = 'TOTAL elapsed: ' + CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) + ' seconds. Total rows: ' + CAST(@TOTALROWS AS VARCHAR) + '. Final Speed: ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) + ' rows per second' RAISERROR (@MSG, 0, 1) WITH NOWAIT