Is that a nice way of saying typo?
People will often tell you to clearly alias your tables, and they’re right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can also help solve odd performance problems.
Take this query, for instance.
1 2 3 4 5 6 7 8 |
SELECT p.Title FROM dbo.Posts AS p WHERE p.Id IN ( SELECT TOP 5 p2.Id FROM dbo.Posts AS p2 WHERE p2.PostTypeId = 1 ORDER BY p2.Score DESC ) |
We have two references to the Posts table, as p and p2. Correctly aliased and referenced, the query finishes instantly with a pretty tidy execution plan and a total cost of 0.0166169.
But what if we mess up? What if we write our query like this, and reference the outer query in the subquery? Our off-kilter where clause wreaks plan havoc.
1 2 3 4 5 6 7 8 |
SELECT p.Title FROM dbo.Posts AS p WHERE p.Id IN ( SELECT TOP 5 p2.Id FROM dbo.Posts AS p2 WHERE p.PostTypeId = 1 <-- First of all, ew. ORDER BY p2.Score DESC ) |
That whole thing goes wonky, with a total cost of 161094000000. This is not helpful.
We get a similarly painful plan if we mess up the ORDER BY, referencing the outer query.
1 2 3 4 5 6 7 8 |
SELECT p.Title FROM dbo.Posts AS p WHERE p.Id IN ( SELECT TOP 5 p2.Id FROM dbo.Posts AS p2 WHERE p2.PostTypeId = 1 ORDER BY p.Score DESC <-- Ew again ) |
This plan is a bit less yucky, and costs a bit less, but is still all wrong. It ‘only’ costs 10705.1 query bucks.
Curiously!
If we don’t alias anything at all, the optimizer gets it right. I’m not trying to give you an excuse to not alias your tables, here. Just that the optimizer is sometimes smart enough to take the easy way out, and we’re back to our original plan and cost.
1 2 3 4 5 6 7 8 |
SELECT Title FROM dbo.Posts WHERE Id IN ( SELECT TOP 5 Id FROM dbo.Posts WHERE PostTypeId = 1 ORDER BY Score DESC ) |
Now, I know this is a silly query, but that’s kind of the whole point. It doesn’t take much to throw performance off a whole lot. Whether it’s a typo or a logical error when writing the query, sometimes you can find easy performance wins just by double checking your work.
Thanks for reading!
4 Comments. Leave new
Ugh, I hate butt filth…
Beware crust punks.
The over-indented close-parentheses gave me a blood clot…
I’m sorry that readable code causes you such distress.