A Long Time Ago
I had to write some hand-off training about query tuning when I was starting a new job.
As part of the training, I had to explain why writing “complicated logic” could lead to poor plan choices.
So I did what anyone would do: I found a picture of a pirate, named him Captain Or, and told the story of how he got Oared to death for giving confusing ORders.
This is something that I unfortunately still see people doing quite a bit, and then throwing their hands up as queries run forever.
I’m going to show you a simple example of when this can go wrong, and also beg and plead for the optimizer team to do something about it.
“Write the query in the simplest way possible”, they said.
So we did, and we got this.
SELECT u.Id, MAX(p.Score) FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId OR u.Id = p.LastEditorUserId WHERE p.PostTypeId IN (1, 2) GROUP BY u.Id;
Note the OR in the join condition — we can match on either of those columns.
Here’s the index we created to make this SUPERFAST.
CREATE NONCLUSTERED INDEX 36chambers ON dbo.Posts ( OwnerUserId, LastEditorUserId, PostTypeId ) INCLUDE ( Score );
If we’re good DBAs, still doing as we’re told, we’ll read the query plan from right to left.
The first section we’re greeted with is this:
We spend a full minute organizing and ordering data. If you want to poke around, the plan XML is here.
The columns in the Compute Scalars are OwnerUserId and LastEditorUserId.
Next in the plan is this fresh hell:
24 seconds seeking into the Users table and joining that to the results of the Constant Scans, etc.
What’s a little confusing here is that the scan on the Posts table occurs on the outer side of Nested Loops.
It’s also responsible for feeding rows through the Constant Scans. That’s their data source.
Overall, this query takes 1:36 seconds to run.
My gripe with it is that it’s possible to rewrite this query in an obvious way to fix the problem.
Using a second join to Posts clears things up quite a bit.
SELECT u.Id, MAX(p.Score) FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId AND p.PostTypeId IN (1, 2) JOIN dbo.Posts AS p2 ON u.Id = p2.LastEditorUserId AND p2.PostTypeId IN (1, 2) GROUP BY u.Id;
I know, it probably sounds counterintuitive to touch a table twice.
Someone will scream that we’re doing more reads.
Someone else will faint at all the extra code we wrote.
But when we run this query, it finishes in 10 seconds.
This plan does something a bit different. It joins the nonclustered index we have on Posts to itself.
The optimizer has a rule that makes this possible, called Index Intersection.
A more accurate description of what I’d want the optimizer to consider here would be the plan we get when we rewrite the query like this.
SELECT u.Id, MAX(p.Score) FROM dbo.Users AS u CROSS APPLY ( SELECT p.Score FROM dbo.Posts AS p WHERE u.Id = p.OwnerUserId AND p.PostTypeId IN (1, 2) UNION ALL SELECT p2.Score FROM dbo.Posts AS p2 WHERE u.Id = p2.LastEditorUserId AND p2.PostTypeId IN (1, 2) ) AS p GROUP BY u.Id;
This query runs a bit faster than the second one (around 7 seconds), and the plan is a little different.
Rather than a Hash Join between the index on the Posts table, we have a Concatenation operator.
The rest of the plan looks like this:
The optimizer has a rule that can produce this plan, too, called Index Union.
The thing is, these rules seem to be favored more with WHERE clauses than with JOINs.
CREATE INDEX ix_fangoria ON dbo.Posts(ClosedDate); SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.ClosedDate IS NULL OR p.ClosedDate >= '20170101' AND 1 = (SELECT 1);
CREATE INDEX ix_somethingsomething ON dbo.Posts(PostTypeId); CREATE INDEX ix_wangchung ON dbo.Posts(AcceptedAnswerId); SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.PostTypeId = 1 AND p.AcceptedAnswerId = 0 AND 1 = (SELECT 1);
It is possible to get these kind of plans with joins, but not without join hints and a couple indexes.
CREATE INDEX aussie ON dbo.Posts (OwnerUserId, PostTypeId, Score); CREATE INDEX kiwi ON dbo.Posts (LastEditorUserId, PostTypeId, Score);
SELECT u.Id, MAX(p.Score) FROM dbo.Users AS u JOIN dbo.Posts AS p WITH (FORCESEEK) ON u.Id = p.OwnerUserId OR u.Id = p.LastEditorUserId WHERE p.PostTypeId IN (1, 2) GROUP BY u.Id;
Even with Paul White ~*~Magick~*~, the hinted query runs for ~16 seconds.
If you remember, the Index Intersection plan ran for around 10 seconds, and the Index Union plan ran for around 7 seconds.
This plan uses Index Union:
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 on 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.
- Using Views To Reduce Memory Grants In SQL Server
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Signs You Need Batch Mode To Make Your SQL Server Queries Faster