in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

Running Massive Updates, Deletes, Inserts

Firstly with large transactions comes large transaction logs, ensure you grow your log accordingly before running a huge transaction because autogrows can cause overhead during execution for example: why did the query run so quickly on test rather than live… the likelihood is you ran it repeatedly on your test box and it had grown already.

If you plan on running a large insert you should grow the data file accordingly, not only will this improve speed but it will also ensure you have enough space for the query to run!

Run in loops/batches

Old way:

Delete From
MainTable
where
ID in(select ID from DeleteList)
 
Loopy way:
declare
@min int,
@max int,
@increments int,
@Counter int

SET @min = (select MIN(ID) from DeleteList)
SET @max = (select MAX(ID) from DeleteList)
SET @increments = 1000
SET @Counter = @min

While(@Counter < @max)
BEGIN
declare @progress varchar(10)

Delete From
MainTable
where
ID between @Counter and @Counter + @increments
and
ID in(select ID from DeleteList)
set @Counter = @Counter + @increments

set @progress = CAST(@counter as varchar(10))
RAISERROR ('PROGRESS %s', 0, 1, @progress)
WITH NOWAIT
END


So why use the loop?

Firstly you are running smaller batches so you’ll only be locking subsets, this is especially important when running on a 24/7 environment, secondly you can use the with (rowlock) option if you wish, you can play around with the batch size to find the optimal update size.

You should also note the following statement:

RAISERROR ('PROGRESS           %s', 0, 1, @progress) 
WITH NOWAIT

When you want to report progress the print statement can be a bad idea as print messages are buffered until the batch completes, the raiserror with nowait option will immediately send the message to you, when running your query if you notice a long delay in messages you are able to react immediately.

I know this is a fairly back to basics post but this is something I’ve had to go back to lots of times so thought it would be worth sharing!

I’ve attached the T-SQL.

Comments

No Comments

About AtulThakor

Twitter:@AtulThakor
Powered by Community Server (Commercial Edition), by Telligent Systems