Rewriting Expensive Updates

Erik Darling takes us through an experiment:

Let’s also say that bad query is taking part in a modification.

UPDATE u2
SET u2.Reputation *= 2
FROM Users AS u
JOIN dbo.Users AS u2
ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0
WHERE u2.Reputation >= 100000;
AND u.Id <> u2.Id;

This query will run for so long that we’ll get sick of waiting for it. It’s really holding up writing this blog post.

Erik rewrites this query a couple of times. Click through to learn what he does and why he does it.

Related Posts

Workload Analysis with Query Store

Erin Stellato shows how you can mine the Query Store tables to learn more about your workload: The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively.  The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max).  This means that the data can be […]

Read More

Getting the Last Actual Plan

Grant Fritchey shows off an improvement in SQL Server 2019: I’ve always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for […]

Read More

Categories

May 2019
MTWTFSS
« Apr Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031