Author: Erik Darling

Off And On

I spend a lot of time fixing queries like this, so I figured that I’d write about it in hopes that I can save someone some time and pain later on.

Obviously, this extends to join clauses as well. A case expression there has just as much chance of causing performance issues.

This pattern crops up in some ORM queries that I’ve seen as well, but I haven’t been able to figure out the code that causes it.

Let’s look at why this causes problems!

Tractor Trailer

To give our query the best possible chance of not sucking, let’s create some indexes.

CREATE NONCLUSTERED INDEX p
ON dbo.Posts 
(
    PostTypeId,
    Score, 
    OwnerUserId
);

CREATE NONCLUSTERED INDEX u
ON dbo.Users 
(
    Reputation
)
INCLUDE 
(
    DisplayName
);

With those in place, let’s look at a simple example query.

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE 1 = 
    CASE 
        WHEN p.PostTypeId = 1
        AND  p.Score >= 10
        THEN 1
        ELSE 0
    END
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

The plan reveals textbook symptoms of a lack of SARGability: an index scan with a predicate, despite a perfectly seekable index being in place:

Shame about that! But we can make things worse, too.

The Worser

If we involve a new column in the case expression, this time from the Users table, the predicate will be applied at a really unfortunate place in the query plan.

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE 1 = 
    CASE 
        WHEN p.PostTypeId = 1
        AND  p.Score >= 10
        AND  u.Reputation > 5000
        THEN 1
        ELSE 0
    END
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

Now all the filtering happens at the join, and the query goes from taking about 1 second to taking about 5 seconds.

If you write queries like this, you’re asking for trouble.

Why Can’t You Just Be Normal?

If we express that logic without a case expression, performance turns out much better. Shocking, right?

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 1
AND   p.Score >= 10
AND   u.Reputation > 5000
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

This query takes about 240 milliseconds, which is a nice improvement.

We’re able to seek into our Super Helpful Index™ on the Posts table. Now I know what you’re thinking — we’re hitting the clustered index on the Users table —  that must be horrible.

But no; because the Nested Loops Join is of the Apply variety, it makes more sense to use it to seek to a single Id, and evaluate the predicate on Reputation second.

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.