2

SQL Server 2014 Standard Edition

We have a table with 100million+ rows.

We need to update values in a couple of columns.

We did the below, and it has been running for 18 hours now, and has only done 17 million rows. It is getting slower and slower.

It is a reasonably hot server and disk system (EMC RAID10 and all that). The CREATE INDEX took about 20 minutes (an unfortunate outage we had to tolerate).

What approach can we use to get through this faster? (on line strongly preferred)

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC );
GO


DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 4000  -- to stay under lock escalation threshhold

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END
4
  • Some subtle differences here - that's for deletes, but same concepts apply for updates. Note that if all you're doing is deleting a bunch of rows in chunks and not doing anything to the transaction log that is generated in each iteration, you're not really changing much about the time that it will take to delete the whole set - in fact you may be making it worse. How many indexes on the table? Any indexed views? – Aaron Bertrand Sep 17 '15 at 15:21
  • @samsmith can you post the table structure with indexes. – Kin Shah Sep 17 '15 at 15:26
  • I hope that table is partitioned? – Dave Sep 17 '15 at 20:36
  • @Dave Nay, we are on standard edition.... no partitioning for us... – Jonesome Reinstate Monica Sep 17 '15 at 22:53
4

It probably would have been better to create the index as filter (i.e. with a WHERE clause) as that would get smaller as you updated more records that would then get filtered out of it:

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX
  ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC )
  WHERE deleted is null
  OR    deletedDate is null;

However, you are still going to run into the issue of scanning that index every time that loop runs. With 100 million rows in the table and updating 4000 per iteration, that is 25,000 times you are scanning the table (or index) looking for rows to update. It would be best if you reduced the number of times you queried the table.

Please see my answer to one of your other questions regarding this project for a setup that should make this process go much faster:

sql server: updating fields on huge table in small chunks: how to get progress/status?

In that answer, I show how to query the large table only 100 times. Each time it grabs the Clustered Index Key Field(s) and uses them for all of the UPDATE queries so that the updates are fast. And that setup also allows for getting the current progress of the operation quickly (and without having to query the table!) and cleanly cancelling the process.


It should be noted that this question relates to the following questions (listed in chronological order):

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.