Navigation

Thursday, November 18, 2010

How to update a large SQL tables without killing the server!

Often I have the need to update a very large (>1 million rows) and a simple sql statement like this takes a long time;

update mytable set myfield = getdate() where myfield is null

This statement provides better performance (if you have triggers on the table disabling them if you can provides better performance also);

DISABLE trigger [dbo].[myTrigger] ON  [dbo].[myTable];
go
SET ROWCOUNT 10000

WHILE 1=1
BEGIN
UPDATE myTable
SET myField = GETDATE()
WHERE myField is null
IF @@ROWCOUNT = 0 BREAK

END
go
enable trigger [dbo].[myTrigger] ON  [dbo].[myTable];
go

By changing the “Set RowCount” value you can tune the statement as needed.

No comments: