Query Bum
Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.
Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.
Oh well, though. Keep me employed, ISVs.
When we are allowed to change indexes, sometimes we can fix problems enough to avoid needing to change the code.
Big Time
Let’s start by creating two different indexes. They have the same key columns, just in different order.
CREATE INDEX v ON dbo.Votes
(UserId, CreationDate);
CREATE INDEX vv ON dbo.Votes
(CreationDate, UserId);
The query that we care about it this one:
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE ISNULL(v.UserId, 0) > 0
AND v.CreationDate >= '20180601';
I know, you’re smart, you’re savvy, you’d never write a query like this. But I see it constantly.
Daring
If we compare query performance using the two different nonclustered indexes, the one that leads with CreationDate is the clear winner.
Bookends
The main advantage of a Seek here is we’re able to seek to a much smaller range of rows first, and then apply the non-SARGable predicate to UserId.
Obviously, scanning 182,692,000 rows is a bit slower than seeking to 32,411,155 rows and applying the residual predicate.
Tomorrows
If you can rewrite queries like this, you absolutely should. That people still write queries like this is a sad testament to… Well, I’m not sure what.
In tomorrow’s post, we’ll look at how dynamic SQL can help ward off non-SARGable predicates.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.
Erik
you say “..That people still write queries like this is a sad testament to… Well, I’m not sure what.” As a dba with 47 years of IT experience, I don’t understand your point….it seems like a business type query to answer a real business question. My suggestion is to change the order in the predicate to the table with the minimal number of rows first….although that is something that the optimizer should not care about or more precisely figure out to handle first in the query plan sometimes it “encourages” the optimizer if you order the predicate from smallest to largest number of rows in case a scan rather than a see is selected. Also, why not have two indices–one for each of the columns and again let the optimizer try and figure out the best plan using that approach.
I’m not sure if you have a point here that’s not coming across in the wall-of-text. It seems like you’re saying that if I change the order of the where clause it will change the order that SQL Server evaluates them in?
If you have 47 years of experience and you’re okay with wrapping columns in the where clause in functions, I’m at a bit of a loss.