Cheating At Candy Crush
The short answer is that yes, they can. But only with a little extra preparation.
Before I show you what I mean, we should probably define what’s not SARGable in general.
- Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc.
- Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col), a_col +b_col, etc.
- Optional predicates: a_col = @a_variable or @a_variable IS NULL
- Applying some expression to a column: a_col * 1000 < some_value
Applying predicates like this show that you don’t predi-care.
They will result in the “bad” kind of index scans that read the entire index, often poor cardinality estimates, and a bunch of other stuff — sometimes a filter operator if the predicate can’t be pushed down to the index access level of the plan.
Most Common
Is the NULL replacement issue, I think.
If I had an index and query like this, life would be grand. I’d be able to perform two seeks into the index and only read ranges of rows that qualify.
|
1 2 3 4 5 6 7 |
CREATE INDEX ix_age ON dbo.Users(Age) INCLUDE (Reputation) SELECT SUM(u.Reputation) AS whatever FROM dbo.Users AS u WHERE u.Age = 0 OR u.Age IS NULL; |
Life gets less grand if I change the query to this. We have to scan the entire index, replace null values with 0, and apply a predicate.
|
1 2 3 |
SELECT SUM(u.Reputation) AS whatever FROM dbo.Users AS u WHERE ISNULL(u.Age, 0) = 0; |
This isn’t disastrous here, but I’d probably want to fix it.
Unless I couldn’t. This may be turd party code that I can’t alter.
The Workaround
We can cheat a little bit, by adding a computed column and indexing it.
|
1 2 3 4 5 |
ALTER TABLE dbo.Users ADD AgeNoNull AS ISNULL(Age, 0); CREATE INDEX ix_agenonull ON dbo.Users(AgeNoNull) INCLUDE (Reputation); |
Which means our original query now gets a Seek plan!
|
1 2 3 |
SELECT SUM(u.Reputation) AS whatever FROM dbo.Users AS u WHERE ISNULL(u.Age, 0) = 0; |
If you compare the number of rows read between the Seek and Scan, the Seek does get just the rows it needs.
Surprises
I was a bit surprised that this worked out well. It’s a bit like function based indexes in other RDMBS platforms. It’s also nice that the expression matching portion of the optimizer was able to pick up on it easily.
Thanks for reading!
Brent says: indexed (not just persisted) computed columns are an awesome trick to have in your performance tuning bag. If you have trouble getting SQL Server to use ’em, read Paul White’s post about trace flag 176.
4 Comments. Leave new
extra performance bonus points is that the seek is ordered output, making use of this in a sub-query or join much cheaper (assuming you’ve another table with the relevant key on hand, ordered).
Erik, correct the WHERE clause in the code that is immediately after “Seekaroni” when possible. There is a losted parenthesis there.
Replace
WHERE ISNULL(u.Age, 0) = 0);
by
WHERE ISNULL(u.Age, 0) = 0;
—
José Diz
https://portosql.wordpress.com
José – great catch, fixed!
[…] Can Non-SARGable Predicates Ever Seek? […]