CODEWITHSUNDEEP
sqlsql-serverdatabase
Infravision
Infravision

Reputation: 139

Updating a large table and minimizing user impact

I have a question on general database/sql server designing:

There is a table with 3 million rows that is being accessed 24x7. I need to update all the records in the table. Can you give me some methods to do this so that the user impact is minimized while I update my table?

Thanks in advance.

Upvotes: 0

Views: 337

Answers (4)

Brian White
Brian White

Reputation: 1314

Normally you'd write a single update statement to update rows. But in your case you actually want to break it up.

http://www.sqlfiddle.com/#!3/c9c75/6 Is a working example of a common pattern. You don't want a batch size of 2, maybe you want 100,000 or 25,000 - you'll have to test on your system to determine the best balance between quick completion and low blocking. 

declare @min int, @max int

select @min = min(user_id), @max = max(user_id)
from users

declare @tmp int
set @tmp = @min

declare @batchSize int
set @batchSize = 2


while @tmp <= @max
begin
  print 'from ' + Cast(@tmp as varchar(10)) + ' to ' + cast(@tmp + @batchSize as varchar(10)) + ' starting (' + CONVERT(nvarchar(30), GETDATE(), 120) + ')'
  update users
  set name = name + '_foo'
  where user_id >= @tmp and user_id < @tmp + @batchsize and user_id <= @max

  set @tmp = @tmp + @batchSize
  print 'Done  (' + CONVERT(nvarchar(30), GETDATE(), 120) + ')'

  WAITFOR DELAY '000:00:001'

end
update users
set name = name + '_foo' 
where user_id > @max

We use patterns like this to update a user table about 10x your table size. With 100,000 chunks it takes about an hour. Performance depends on your hardware of course.

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 416053

As with all things database, it depends. What is the load pattern (ie, are users reading mainly from the end of the table)? How are new records added, if at all? What are your index fill factor settings and actual values? Will your update force any index re-computes? Can you split up the update to reduce locking? If so, do you need robust rollback ability in case of a failure? Are you setting the same value in every row, or do you need a per row calculation, or do you have a per-row source to match up?

Upvotes: 0

Sebastian Meine
Sebastian Meine

Reputation: 11813

Go through the table one row at a time using a loop or even a cursor. Make sure each update is using row locks.

If you don't have a way of identifying rows that still have to be updated, create another table first to hold the primary key and an update indicator, copy all primary key values in there and then keep track of how far you are along in that table.

This is also going to be the slowest method. If you need it to go a little faster, update a few thousand rows at a time, still using rowlock hints.

Upvotes: -1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

To minimally impact users, I would update only a certain # of records at a time. The number to update is more dependent on your hardware than anything else in my opinion.

Upvotes: 0

Related Questions