Let’s say we have a couple of update statements we need to run every 15 minutes in the Stack Overflow database, and we’ve built indexes to support them:
Transact-SQL
12345678910111213141516 | EXEC DropIndexes @TableName = N'Users';CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);CREATE INDEX AccountId ON dbo.Users(AccountId);GO BEGIN TRAN /* Give reputation points if folks didsomething in the last 15 minutes: */UPDATE dbo.Users SET Reputation = Reputation + 100 WHERE LastAccessDate >= DATEADD(MINUTE, -15, GETDATE()); /* If we're having account sync problems (AccountId = 0),reset their name/location: */UPDATE dbo.Users SET DisplayName = N'Unknown', Location = NULL WHERE AccountId = 0; |
I’ve got a BEGIN TRAN in there before the updates just so I can test the same queries repeatedly, and roll them back each time. The execution plan for the updates is quite nice: SQL Server divebombs into the supporting indexes:
Relatively few rows match, so our query does less than 1,000 logical reads – way less than there are pages in the table. In this case, separate UPDATE statements make sense.
However:
The more update statements you have
The more columns they affect
The more rows they affect (because they have less selective WHERE clauses, especially when they’re up over the lock escalation threshold)
The more unpredictable their WHERE clauses are (like SQL Server gets the row estimates hellaciously wrong)
Then it can actually make sense to rewrite the query into a single faster (albeit heinous) UPDATE statement. To illustrate, let’s pretend that instead of giving reputation points away every 15 minutes, let’s say we only give them out once per day, to people who accessed the system yesterday. I’m going to use a hard-coded date in my WHERE clause because my Stack Overflow database didn’t have activity yesterday:
Transact-SQL
1234567891011 | BEGIN TRAN/* Give reputation points if folks didsomething yesterday: */UPDATE dbo.Users SET Reputation = Reputation + 100 WHERE LastAccessDate >= '2018-06-02' AND LastAccessDate < '2018-06-03'; /* If we're having account sync problems (AccountId = 0),reset their name/location: */UPDATE dbo.Users SET DisplayName = N'Unknown', Location = NULL WHERE AccountId = 0; |
Now, because we’re awarding points to more people, our actual execution plan looks different:
And it does a lot more logical reads because it’s scanning the table, plus doing the work in the second update.
If the performance problem you’re facing is multiple table scans – and that’s an important distinction that we’ll come back to – then it may make sense to rewrite the multiple update statements into a single, albeit heinous, T-SQL statement:
Transact-SQL
1234567891011 | BEGIN TRANUPDATE u SET Reputation = CASE WHEN LastAccessDate >= '2018-06-02' AND LastAccessDate < '2018-06-03' THEN Reputation + 100 ELSE Reputation END, DisplayName = CASE WHEN AccountId = 0 THEN N'Unknown' ELSE DisplayName END, Location = CASE WHEN AccountId = 0 THEN NULL ELSE Location ENDFROM dbo.Users uWHERE (LastAccessDate >= '2018-06-02' AND LastAccessDate < '2018-06-03') OR AccountId = 0; |
Here, the WHERE clause pulls in all rows that matched EITHER update statement before. Then we’re setting EVERYONE’S Reputation, DisplayName, and Location – but we’re either setting it to its original value, or we’re setting it to the required update value.
Is this easier to read or debug? Absolutely not. However, it gets us down to just one table scan:
In the client example that prompted this blog post, there were half a dozen update statements, each of which did a table scan on a giant table way too big to fit into memory, so storage was getting hammered and the buffer pool kept getting flushed due to this issue.
However, only do this if you need to solve the multi-scan problem. Most situations are just fine with multiple isolated updates running in a row, and it’s fairly unusual that I need to solve for the multi-scan issue. When I do have to implement the single-update solution, it comes with a few problems. Say you’re dealing with 3 updates, and each of them update a different 1/3 of a 1TB table. Before, each update was only changing 1/3 of the table at a time, which meant:
Each update wrote 1/3 of the table into the transaction log
Each update wrote 1/3 of the table to its Availability Group neighbors
Each update wrote 1/3 of the table into the Version Store
Each update only needed enough memory to sort 1/3 of the table (due to the particulars of the update statement involved, there was a memory grant)
But if we combined those 3 update statements, then we’d be dealing with one massive update that caused all kinds of problems with the logs, HA/DR, Version Store, memory grants, and TempDB spills. A single update might make things far worse, and in fact, we’d probably be better off doing our changes in small batches like I talk about in this module of the Mastering Query Tuning class.
What’s that, you say?
Comments