If you landed here from Brent’s weekly links, use this link to get my training for 90% off.
The access is for life, but this coupon code isn’t! Get it while it lasts.
Discount applies at checkout, and you have to purchase everything for it to apply.
There are going to be situations where it’s smarter to change different aspects of code like this:
But I know how it is out there! Sometimes it’s hard to get in and change a bunch of logic and tinker with things.
In some cases, you can improve performance by wrapping chunks of code in transactions.
Fear Of Commitment
In this example, there’s an automatic commit every time the update completes. That means every time we step through the loop, we send a record to the transaction log.
This can result in very chatty behavior, which even good storage can have a tough time with. There are likely other aspects of transaction logging impacted by this, but I only have so much time before this call starts.
SET NOCOUNT ON; DECLARE @cur_user int = 0, @max_user int = 0; SELECT @cur_user = MIN(u.Id), @max_user = MAX(u.Id) FROM dbo.Users AS u WHERE u.Age IS NULL; WHILE @cur_user <= @max_user BEGIN UPDATE u SET u.Age = DATEDIFF(YEAR, u.CreationDate, u.LastAccessDate) FROM dbo.Users AS u WHERE u.Id = @cur_user AND u.Age IS NULL; SET @cur_user = (SELECT MIN(u.Id) FROM dbo.Users AS u WHERE u.Id > @cur_user); END;
This code runs for nearly 5 minutes before completing. Looking at a ~60 second sample turns up some gnarly gnumbers.
Without changing the logic of the update, we can get things in better shape by using transactions and periodically committing them.
SET NOCOUNT ON; DECLARE @rows bigint = 0, @cur_user int = 0, @max_user int = 0; SELECT @cur_user = MIN(u.Id), @max_user = MAX(u.Id) FROM dbo.Users AS u WHERE u.Age IS NULL; BEGIN TRANSACTION; WHILE @cur_user <= @max_user BEGIN UPDATE u SET u.Age = DATEDIFF(YEAR, u.CreationDate, u.LastAccessDate) FROM dbo.Users AS u WHERE u.Id = @cur_user AND u.Age IS NULL; IF @rows = (@rows + @@ROWCOUNT) BEGIN COMMIT TRANSACTION; RETURN; END; ELSE BEGIN SET @rows = (@rows + @@ROWCOUNT); SET @cur_user = (SELECT MIN(u.Id) FROM dbo.Users AS u WHERE u.Id > @cur_user AND u.Age IS NULL); END; IF @rows >= 50000 BEGIN RAISERROR('Restarting', 0, 1) WITH NOWAIT; SET @rows = 0; COMMIT TRANSACTION; BEGIN TRANSACTION; END; END; IF @@TRANCOUNT > 0 COMMIT
The first thing we’ll notice is that the code finishes in about 1 minute rather than 5 minutes.
How nice! I love when things move along. The metrics look a bit better, too.
We have almost no waits on WRITELOG, and we write far less to the transaction log (35MB vs 13MB).
We also got to do some snazzy stuff with @@ROWCOUNT. Good job, us.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.