There are some things that, in the course of normal query writing, just can’t be SARGablized. For example, generating and filtering on a windowing function, a having clause, or any other runtime expression listed here.
There are some interesting ways to use indexed views to our advantage for some of those things. While windowing functions and having clauses can’t be directly in an indexed view, we can give an indexed view a good definition to support them.
I Don’t Care For It
Starting with these indexes to help things along, they don’t really do as much as we’d hope.
CREATE INDEX c ON dbo.Comments (PostId); CREATE INDEX v ON dbo.Votes (PostId); CREATE INDEX p ON dbo.Posts (Id, OwnerUserId, Score);
This query has to process a ton of rows, and no matter what we set the having expression to, the entire result set has to be generated before it can be applied. We could set it to
> 0 or
> infinity and it would take the same amount of time to have a working set to apply it to.
SELECT p.OwnerUserId, TotalScore = SUM(ISNULL(p.Score * 1., 0.)), records = COUNT_BIG(*) 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 SUM(ISNULL(p.Score * 1., 0.)) > 5000000. ORDER BY TotalScore DESC;
I know that having clause looks funny there, but it’s not my fault. The sum of Score ends up being a really big integer, and overflows the regular sized integers unless you explicitly convert it to a bigint or implicitly convert it to something floaty. The isnull is there because the column is NULLable, which is unacceptable to an indexed view.
So, here we are, forced to write something weird to conform.
Ignoring the woefully misaligned and misleading operator times, we can see in the query plan that again(!) a late Filter operator is applied that aligns with the predicate in our having clause.
Let’s work some magic, here.
CREATE VIEW dbo.BunchaCrap WITH SCHEMABINDING AS SELECT p.OwnerUserId, TotalScore = SUM(ISNULL(p.Score * 1., 0.)), records = COUNT_BIG(*) 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; GO CREATE UNIQUE CLUSTERED INDEX bc ON dbo.BunchaCrap(OwnerUserId);
This gives us an indexed view with the TotalScore expression materialized, which means we can search on it directly now without all the 50 some-odd seconds of nonsense leading up to it.
At The Mall
Our options now are either to query the indexed view directly with a noexpand hint, or to run the query as initially designed and rely on expression matching to pick up on things.
SELECT bc.* FROM dbo.BunchaCrap AS bc WITH (NOEXPAND) WHERE bc.TotalScore > 5000000. ORDER BY bc.TotalScore DESC;
In either case, we’ll get this query plan now:
Which looks a whole heck of a lot nicer.
Tomorrow, we’ll look at how implicit conversion can look a lot like non-SARGable predicates.
Thanks for reading!