Source Of Frustration
When we write queries that need to filter data, we tend to want to have that filtering happen as far over to the right in a query plan as possible. Ideally, data is filtered when we access the index.
Whether it’s a seek or a scan, or if it has a residual predicate, and if that’s all appropriate isn’t really the question.
In general, those outcomes are preferable to what happens when SQL Server is unable to do any of them for various reasons. The further over to the right in a query plan we can reduce the number of rows we need to contend with, the better.
There are some types of filters that contain something called a “startup expression”, which are usually helpful. This post is not about those.
Ain’t Nothin’ To Do
There are some cases when you have no choice but to rely on a Filter to remove rows, because we need to calculate some expression that we don’t currently store the answer to.
For example, having:
SELECT p.OwnerUserId, COUNT_BIG(*) AS records FROM dbo.Posts AS p JOIN dbo.Comments AS c ON c.PostId = p.Id JOIN dbo.Votes AS v ON v.PostId = p.Id GROUP BY p.OwnerUserId HAVING COUNT_BIG(*) > 2147483647;
We don’t know which rows might qualify for the count filter up front, so we need to run the entire query before filtering things out:
There’s a really big arrow going into that Filter, and then nothing!
Likewise, filtering on the result of a windowing function will get you a similar execution plan.
Of course, there’s not a lot to be done about these Filters, is there?
Unless you pre-compute things somewhere else, you have to figure them out at runtime.
If you write yourself a left join, Filters may become more common, too.
You might do something terrible:
SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u LEFT JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE DATEDIFF(YEAR, p.CreationDate, p.LastActivityDate) > 5;
Or you might do something that seems reasonable:
SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u LEFT JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE p.Id IS NULL;
But what you get is disappointing!
What we care about here is that, rather than filtering rows out when we touch indexes or join the tables, we have to fully join the tables together, and then eliminate rows afterwards.
This is generally considered “less efficient” than filtering rows earlier. Remember when I said that before? It’s still true.
Click the links above to see some solutions, so you don’t feel left hanging by your left joins.
If you see Filters in query plans, they might be for a good reason, like calculating things you don’t currently know the answer to.
They might also be for bad reasons, like you writing a query in a silly way.
There are other reasons they might show up too, that we’ll talk about tomorrow.
Why tomorrow? Why not today? Because if I keep writing then I won’t take a shower and run errands for another hour and my wife will be angry.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.