Tag: Deadlock

Followers

Common table expressions remind me of clothes in the 70s. A bunch of people with no taste convinced a bunch of people with no clue that they should dress like them, and so we got… Well, at least we got the 80s afterwards.

The big draw with common table expressions is that they filled in some blanks that derived tables left unanswered.

• The first problem with common table expressions is that most people use them like nose and ear hair trimmers: they just sort of stick them in and wave them around until they’re happy, with very little observable feedback as to what has been accomplished.
• The second big problem with common table expressions is that the very blanks they were designed to fill in are also the very big drawbacks they cause, performance-wise. Sort of like a grand mal petard hoisting.

To bring things full circle, asking someone why they used a common table expression is a lot like asking someone why they wore crocheted bell bottoms with a velour neckerchief in the 70s. Someone said it was a good idea, and… Well, at least we got the 80s afterwards.

Much like joins and Venn diagrams, anyone who thinks they have some advanced hoodoo to teach you about common table expressions is a charlatan or a simpleton. They are one of the least advanced constructs in T-SQL, and are no better or worse than any other abstraction layer, with the minor exception that common table expressions can be used to build recursive queries.

Other platforms, enviably, have done a bit to protect developers from themselves, by offering ways to materialize common table expressions. Here’s how Postgres does it, which is pretty much the opposite of how SQL Server does it.

By default, and when considered safe, common table expressions are materialized to prevent re-execution of the query inside them.

You can force the issue by doing this (both examples are from the linked docs):

WITH w AS MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

Or go your own way and choose to not materialize it:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

You don’t get those options in SQL Server as of this writing, which really sucks because developers using other platforms may have certain expectations that are, unfortunately, not met.

Likewise, other sane and rational platforms use MVCC (optimistic locking) by default, which SQL Server does not. Another expectation that will unfortunately not be met for cross-platform developers.

Common Table Cult

The amount of developer-defense that common table expressions get is on par with the amount of developer-defense that table variables get.

It’s quite astounding to witness. How these things became such sacred cows is beyond me.

First, there are times when using a common table expression has no impact on anything:

WITH
    nocare AS
(
    SELECT
        u.*
    FROM dbo.Users AS u
    WHERE u.Reputation > 999999
)
SELECT
    n.*
FROM nocare AS n;


WITH
    nocare AS
(
    SELECT
        u.*
    FROM dbo.Users AS u
)
SELECT
    *
FROM nocare AS n
WHERE n.Reputation > 999999;

SQL Server is at least smart enough to be able to push most predicates used outside of common table expressions up into the common table expression.

One example of such a limitation is when you put a windowing function into a common table expression:

WITH 
    nocare AS 
(   
    SELECT   
        v.*,
        n = 
            ROW_NUMBER() OVER 
            ( 
                PARTITION BY 
                    v.UserId
                ORDER BY 
                    v.CreationDate 
            )
    FROM dbo.Votes AS v 
)
SELECT  
    n.*
FROM nocare AS n
WHERE n.VoteTypeId = 8
AND   n.n = 0;

If VoteTypeId were in the PARTITION BY clause of the windowing function, it could be pushed into the common table expression portion of the query. Without it in there, it has to be filtered out later, when the where clause also looks for rows numbered as 0.

But this does bring us to a case where common table expressions are generally okay, but would perform equivalently with a derived table: when you need to stack some logic that can’t be performed in a single pass.

Using a common table expression to filter out the results of a windowing function just can’t be done without some inner/outer context.  Since objects in the select list are closer than they appear, you can’t reference them in the where clause directly.

I’m fine with that, as shown in the example above.

Common Stacks

Stacked common table expressions are also “fine” up to a point, and with caveats.

One of my absolute favorite paging methods uses stacked common table expressions.

DECLARE
    @page_number int = 1,
    @page_size int = 100;

WITH
    f /*etch*/ AS
(
    SELECT TOP (@page_number * @page_size)
        p.Id,
        n =
            ROW_NUMBER() OVER
            (
                ORDER BY
                    p.Id
            )
    FROM dbo.Posts AS p
    ORDER BY
        p.Id
),
    o /*ffset*/ AS
(
    SELECT TOP (@page_size)
        f.id
    FROM f
    WHERE f.n >= ((@page_number - 1) * @page_size)
    ORDER BY
        f.id
)
SELECT  
    p.*
FROM o
JOIN dbo.Posts AS p
  ON o.id = p.Id
ORDER BY
    p.Id;

The reason why this is okay is because each common table expression has a single reference. There are two points in the query plan where data is acquired from the Posts table.

Where things get tricky is when you keep doing it over and over again.

Attack Stacks

Take a query like this, and imagine what the query plan will look like for a moment.

WITH
    top5 AS
(
    SELECT
        p.*,
        n = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    p.OwnerUserId
                ORDER BY
                    p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = 22656
    AND   p.PostTypeId = 1
)
SELECT
    u.DisplayName,
    t1.Title,
    t2.Title,
    t3.Title,
    t4.Title,
    t5.Title
FROM dbo.Users AS u
LEFT JOIN top5 AS t1
  ON  t1.OwnerUserId = u.Id
  AND t1.n = 1
LEFT JOIN top5 AS t2
  ON  t2.OwnerUserId = u.Id
  AND t2.n = 2
LEFT JOIN top5 AS t3
  ON  t3.OwnerUserId = u.Id
  AND t3.n = 3
LEFT JOIN top5 AS t4
  ON  t4.OwnerUserId = u.Id
  AND t4.n = 4
LEFT JOIN top5 AS t5
  ON  t5.OwnerUserId = u.Id
  AND t5.n = 5
WHERE t1.OwnerUserId IS NOT NULL;

An utter disaster, predictably:

We hit the Posts table a total of five times, or once for each reference back to the original common table expression.

This is not a good use of a common table expression, and is a pattern in general to avoid when using them.

Think of common table expressions sort of like ordering a Rum Martinez. You might be happy when the results eventually show up, but every time you say “Rum Martinez”, the bartender has to go through the whole process again.

There’s no magickal pitcher of Rum Martinez sitting around for your poor bartender to reuse.

That’s called a Shirley Temp Table.

Pivot Peeve

This particular query could use a temp table to materialize the five rows, and re-joining to that would be cheap and easy, even five times, since it’s only five rows going in.

WITH
    top5 AS
(
    SELECT
        p.*,
        n = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    p.OwnerUserId
                ORDER BY
                    p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = 22656
    AND   p.PostTypeId = 1
)
SELECT
    t.OwnerUserId,
    t.Title,
    n
INTO #top5
FROM top5 AS t
WHERE t.n <= 5;

You could also also just PIVOT this one, too:

WITH
    u AS 
(
    SELECT TOP (5)
        u.DisplayName,
        p.Title,
        n = ROW_NUMBER() OVER (ORDER BY p.Score DESC)
    FROM dbo.Posts AS p
    JOIN dbo.Users AS u
      ON p.OwnerUserId = u.Id
    WHERE p.OwnerUserId = 22656
    AND   p.PostTypeId = 1
    ORDER BY
        p.Score DESC
)
SELECT
    p.*
FROM u AS u
PIVOT
(
    MAX(Title)
    FOR n IN ([1], [2], [3], [4], [5])
) AS p;

For all the problems PIVOT can cause when misused, this is a full 19 seconds faster than our most precious common table expression query.

With a half-decent index, it’d probably finish in just about no time.

I’d take this instead any day.

A Note On Recursion

There may be times when you need to build a recursive expression, but you only need the top N children, or you want to get rid of duplicates in child results.

Since you can’t use DISTINCT, TOP, or OFFSET/FETCH directly in a recursive common table expression, some nesting is required.

Of course, we can’t currently nest common table expressions, and to be clear, I think that idea is dumb and ugly.

If Microsoft gives us nested common table expressions before materialized common table expressions, I’ll never forgive them.

WITH 
    postparent AS 
(
    SELECT 
        p.Id, 
        p.ParentId, 
        p.OwnerUserId,
        p.Score,
        p.PostTypeId,
        Depth = 0,
        FullPath = CONVERT(varchar, p.Id)
    FROM dbo.Posts AS p
    WHERE p.CreationDate >= '20131229'
    AND   p.PostTypeId = 1

    UNION ALL

    SELECT
        p2.Id,
        p2.ParentId,
        p2.OwnerUserId,
        p2.Score,
        p2.PostTypeId,
        p2.Depth,
        p2.FullPath
    FROM
    (
        SELECT
            p2.Id, 
            p2.ParentId, 
            p2.OwnerUserId,
            p2.Score, 
            p2.PostTypeId,
            Depth = pp.Depth + 1,
            FullPath = CONVERT(VARCHAR, CONCAT(pp.FullPath, '/', p2.Id)),
            n = ROW_NUMBER() OVER (ORDER BY p2.Score DESC)
        FROM postparent pp
        JOIN dbo.Posts AS p2
          ON pp.Id = p2.ParentId
        AND p2.PostTypeId = 2
    ) AS p2
    WHERE p2.n = 1
)
SELECT 
    pp.Id,
    pp.ParentId,
    pp.OwnerUserId,
    pp.Score,
    pp.PostTypeId,
    pp.Depth,
    pp.FullPath
FROM postparent AS pp
ORDER BY
    pp.Depth
OPTION(MAXRECURSION 0);

To accomplish this, you need to use a derived table, filtering the ROW_NUMBER function outside of it.

This is a more common need than most developers realize when working with recursive common table expressions, and can avoid many performance issues and max recursion errors.

It’s also a good way to show off to your friends at disco new wave parties.

Common Table Ending

Common table expressions can be handy to add some nesting to your query so you can reference generated expressions in the select list as filtering elements in the where clause.

They can even be good in other relatively simple cases, but remember: SQL Server does not materialize results, though it should give you the option to, and the optimizer should have some rules to do it automatically when a common table expression is summoned multiple times, and when it would be safe to do so. I frequently pull common table expression results into a temp table, both to avoid the problems with re-referencing them, and to separate out complexity. The lack of materialization can be hell on cardinality estimation.

In complicated queries, they can often do more harm than good. Excuses around “readability” can be safely discarded. What is “readable” to you, dear human,  may not be terribly understandable to the optimizer. You’re not giving it any better information by using common table expressions, nor are you adding any sort of optimization fence to any queries in them without the use of TOP or OFFSET/FETCH. Row goals are a hell of a drug.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Sweetness, Sweetness

I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:

• When I have a list of literal values

That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.

For IN clauses, it’s far less of an ordeal, usually. But for NOT IN, there are some additional concerns around NULLable columns.

Of course, actual NULL values really screw things up, but even when SQL Server needs to protect itself against potential NULL values, you can end up in performance hospice.

Historical

First, a little bit of history. With NOT IN. Take the below script, and flip the insert into the @bad table variable to use 2 instead of NULL, after you’ve seen what happens with NULL.

DECLARE
    @good table 
(
    id int NOT NULL   
);

DECLARE
    @bad table
(
    id int NULL
);

INSERT
    @good
(
    id
)
VALUES
    (1);

INSERT
    @bad
(
    id
)
VALUES
    (NULL); /*Change this between NULL and 2*/

SELECT
    records =
        COUNT_BIG(*) /*Should be 1, or something*/
FROM @good AS g
WHERE g.id NOT IN
(
    SELECT
        b.id
    FROM @bad AS b
);

You’ll see pretty quickly that NOT IN gives you wonky results when it hits a NULL.

This is known.

Imaginary NULLs

Let’s take the below setup. Though each table allows NULLs in their single column, no NULL values will be inserted into them.

CREATE TABLE
    #OldUsers
(
    UserId int NULL
);

CREATE TABLE
    #NewUsers
(
    UserId int NULL
);

/*
But neither one will have any NULL values at all!
*/
INSERT
    #OldUsers WITH (TABLOCK)
(
    UserId
)
SELECT
    p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId IS NOT NULL;

INSERT
    #NewUsers WITH (TABLOCK)
(
    UserId
)
SELECT
    c.UserId
FROM dbo.Comments AS c
WHERE c.UserId IS NOT NULL;

The real lesson here is that if you know that no NULL values are allowed into your tables, you should specify the columns as NOT NULL.

I know, it’s scary. Really scary. Errors. What if. How dare.

But these are the kind of adult decisions you’ll have to make as an application developer.

Be brave.

Protect Your NULL

The big problem with NOT IN, is that SQL Server goes into defensive driving mode when you use it under NULLable conditions.

I don’t have a license because it would be irresponsible, and I’ve lived my entire life in big cities where having a car would be more trouble than it’s worth. But I assume that when I see people complain about drivers not knowing what to do the second there’s a rain drizzle or a snow flurry somewhere in the area is a similarly annoying scenario out there on the roads. All of a sudden, seemingly competent drivers turn into complete basket cases and drive like they’ve got a trunk full of dead bodies clowns.

Here’s an example of a bad way to deal with the situation, vs. a good way to deal with the situation:

/*Bad Way*/
SELECT
    records = COUNT_BIG(*)
FROM #NewUsers AS nu
WHERE nu.UserId NOT IN
(
    SELECT
        ou.UserId
    FROM #OldUsers AS ou
);

/*Good Way*/
SELECT
    records = 
        COUNT_BIG(*)
FROM #NewUsers AS nu
WHERE NOT EXISTS
(
    SELECT
        1/0
    FROM #OldUsers AS ou
    WHERE nu.UserId = ou.UserId
);

Note the very professional formatting and correct syntax. Ahem. Bask.

Count to 10 while you’re basking.

Results

The resulting execution plan for each example should be illuminating. Here they are:

The NOT IN version takes 15 minutes, and the NOT EXISTS version takes 415ms.

Reality Bites

Since there are no NULLs, the first query returns the correct results. But the amount of work SQL Server has to do to make sure there are no NULLs is both absurd and preposterous.

If you’re like me, and you want to throw the query optimizer in the garbage every time you see a TOP over a Scan, you might say something like “an index would make this really fast”.

You wouldn’t be wrong, but most people either:

• Take bad advice and never index #temp tables
• Create nonclustered indexes on #temp tables that don’t get used

Well, you get what you deserve pay for.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Situations

I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.

One of the most common questions I get is when developers should consider using apply over other join syntax.

The short answer is that I start mentally picturing the apply syntax being useful when:

• There’s a small outer table (FROM), and a large inner table (APPLY)
• I need to do a significant amount of work on the inner side of a join
• The goal of the query is top N per group, or something similar
• I’m trying to get parallel nested loops instead of some alternative plan choice
• To replace a scalar UDF in the select list with an inline UDF
• In order to use the VALUES construct in an odd way

Most of this is situational, and requires a bit of practice and familiarity to spot quickly.

Both cross and outer apply can be used in similar ways to subqueries in the select list, with the added bonus that you can return multiple columns and rows with apply, which you can’t do in a normal subquery.

What Apply Does

The way to think about what apply is doing when a query runs is supplying a table-valued result on the inner side of a join for each row supplied by the outer side of a join.

Here’s a simple example:

SELECT
    u.Id,
    u.DisplayName,
    u.Reputation,
    p.Title,
    p.Score,
    p.CreationDate,
    p.LastActivityDate
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT
        p.*
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = u.Id
    AND   p.PostTypeId = 1
    ORDER BY
        p.CreationDate DESC
    OFFSET 0 ROWS
    FETCH NEXT 3 ROWS ONLY
) AS p
WHERE u.CreationDate >= '20131230'
ORDER BY
    u.Reputation DESC,
    p.CreationDate DESC;

We’re getting everyone from the Users table who Posted a Question in the final days of 2013, ordered by when it was Created.

For every qualifying User, we get a tabular result showing the Title, Score, CreationDate, and LastActivityDate of their question.

You can picture it sort of like this:

Some Users have have more than three results, and some may have fewer than three results, but since the query is self-limited to only the first three, our query sets a row goal and quits once three are found.

More About The Query

Because the Users table is correlated by Id to the Posts table on OwnerUserId, we need to make sure we have a good index to make that access seekable.

Also because we’re only looking for Questions (PostTypeId = 1), and ordering by the most recent ones (CreationDate DESC), it’s also a wise idea to have those in the key.

It’s also worth talking over an interesting point in the query itself: The select from Posts inside of the apply is doing a select star, sure, but the outer query is only pulling a few of the columns. The optimizer can recognize this, which means we don’t need a gigantic covering index to make this query fast. We also… Don’t really need a covering index at all in this case. Just the key columns are good enough.

CREATE INDEX 
    u 
ON dbo.Users 
    (CreationDate, Reputation, Id) 
INCLUDE 
    (DisplayName) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE); 

CREATE INDEX 
    p 
ON dbo.Posts 
    (OwnerUserId, PostTypeId, CreationDate) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Efficiency Unit

An efficient query plan when using apply will look like this:

An inefficient query plan using apply will look like this:

If you’re experimenting with apply, either cross or outer, pay close attention to the query plans. If someone says “cross apply is always slow”, you can bet they stink at indexes.

Choices, Choices

The choice to use apply at all depends on the goal of the query, and the goals of the query tuner. It’s not always a magic performance bullet, but under the right circumstances, it can really make things fly.

The choice to use cross or outer apply depends on the semantics of the starting query. An inner join commutes easily to cross apply, and a left join commutes easily to outer apply.

One important difference in how the joins are implemented is in the optimizer’s choice between normal nested loops, where the join is done at the nested loops operator, and apply nested loops, which is when the join keys are pushed to an index seek on the inner side of the join.

For an in-depth look at that, I’d suggest reading Paul White’s post, Apply versus Nested Loops Join.

The optimizer is capable of transforming an apply to a join and vice versa. It generally tries to rewrite apply to join during initial compilation to maximize the searchable plan space during cost-based optimization. Having transformed an apply to a join early on, it may also consider a transformation back to an apply shape later on to assess the merits of e.g. an index loops join.

Just writing a query using apply doesn’t guarantee that you get the apply nested loops version of a nested loops join. Having solid indexes and easy to search predicates can help push things in the right direction.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Flash Boy

I think subqueries in select lists are very neat things. You can use them to skip a lot of additional join logic, which can have all sorts of strange repercussions on query optimization, particularly if you use have to use left joins to avoid eliminating results.

Subqueries do have their limitations:

• They can only return one row
• They can only return one column

But used in the right way, they can be an excellent method to retrieve results without worrying about what kind of join you’re doing, and how the optimizer might try to rearrange it into the mix.

Since subqueries are in the select list, and can’t eliminate rows from results, you will most commonly see them incorporated late in query plans (much further to the left) as outer joins. The optimizer is generally smart enough to retrieve data for select list subqueries after as much other filtering that can have been applied is applied, so they can be evaluated for as few rows in the outer results as possible.

The most important thing you can do as a developer to make sure your select list subqueries are fast is to make sure you have good supporting indexes for them.

Well Done

Let’s start with this query:

SELECT
    u.Id,
    u.DisplayName,
    u.Reputation,
    TopQuestionScore =     
    (
        SELECT TOP (1)
            p.Score
        FROM dbo.Posts AS p
        WHERE p.PostTypeId = 1
        AND   p.OwnerUserId = u.Id
        ORDER BY
            p.Score DESC,
            p.Id DESC
    ),
    TopAnswerScore =     
    (
        SELECT TOP (1)
            p.Score
        FROM dbo.Posts AS p
        WHERE p.PostTypeId = 2
        AND   p.OwnerUserId = u.Id
        ORDER BY
            p.Score DESC,
            p.Id DESC
    ),
    TotalPosts =    
    (
        SELECT
            COUNT_BIG(*)
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId = u.Id
        AND   p.PostTypeId IN (1, 2)
    )
FROM dbo.Users AS u
WHERE u.Reputation > 500000
ORDER BY
    u.Reputation DESC;

The goal is to find every User with a Reputation over 500,000, and then find their:

• Top scoring question (with a unique tie-breaker on most recent post id)
• Top scoring answer (with a unique tie-breaker on most recent post id)
• Total questions and answers

You might look at this query with a deep sense of impending dread, wondering why we should make three trips to the Posts table to get this information. I totally get that.

But let’s say we have these indexes in place:

CREATE INDEX 
    u
ON dbo.Users 
    (Reputation, Id)
INCLUDE 
    (DisplayName)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX
    p
ON dbo.Posts
    (OwnerUserId, PostTypeId, Score)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

They have everything we need to support quick, navigational lookups.

Query Planner

The query plan for this arrangement looks like this, finishing in 23 milliseconds.

If you write select list subqueries, and they’re terribly slow, there’s a very good chance that the indexes you have in place are not up to the job, particularly if you see Eager Index Spools in the query plan.

All of the time in the plan is spent in the final subquery, that counts the total number of questions and answers. But even that, at 23 milliseconds, is not worth heaving our chests over.

Three round trips are not at all a problem here, but let’s compare.

One Way Ticket

I’m not opposed to experimentation. After all, it’s a great way to learn, observe, and become enraged with the state of query optimization generally.

Here are two rewrites of the above query, to only make one trip to the Posts table. The first one uses a derived join, and the second uses apply. They’re both written to use outer joins, to match the semantics of the three subqueries:

/*
Join
*/
SELECT
    u.Id,
    u.DisplayName,
    u.Reputation,
    p.TopQuestionScore,
    p.TopAnswerScore,
    p.TotalPosts
FROM dbo.Users AS u
LEFT JOIN
(
    SELECT
        p.OwnerUserId,
        TopQuestionScore = 
            MAX(CASE WHEN p.PostTypeId = 1 THEN p.Score ELSE NULL END),
        TopAnswerScore = 
            MAX(CASE WHEN p.PostTypeId = 2 THEN p.Score ELSE NULL END),
        TotalPosts = 
            COUNT_BIG(*)
    FROM dbo.Posts AS p
    WHERE p.PostTypeId IN (1, 2)
    GROUP BY
        p.OwnerUserId
) AS p
  ON p.OwnerUserId = u.Id
WHERE u.Reputation > 500000
ORDER BY
    u.Reputation DESC;

/*
Apply
*/
SELECT
    u.Id,
    u.DisplayName,
    u.Reputation,
    p.TopQuestionScore,
    p.TopAnswerScore,
    p.TotalPosts
FROM dbo.Users AS u
OUTER APPLY
(
    SELECT
        p.OwnerUserId,
        TopQuestionScore = 
            MAX(CASE WHEN p.PostTypeId = 1 THEN p.Score ELSE NULL END),
        TopAnswerScore = 
            MAX(CASE WHEN p.PostTypeId = 2 THEN p.Score ELSE NULL END),
        TotalPosts = 
            COUNT_BIG(*)
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = u.Id 
    AND   p.PostTypeId IN (1, 2)
    GROUP BY
        p.OwnerUserId
) AS p
WHERE u.Reputation > 500000
ORDER BY
    u.Reputation DESC;

A somewhat brief digression here: Query rewrites to use specific syntax arrangements are often not available in ORMs. Many times while working with clients, we’ll stumble across a bushel of quite painful application-generated queries. I’ll show them useful rewrites to improve things, and we’ll all marvel together at how much better things are. I’ll suggest implementing the rewrite as a stored procedure, and all hell will break loose.

Please don’t be one of those developers. Understand the limitations of the technology stack you’re working with. Not everything produced by code is good.

Compare and Contrast

In this case, both of the attempts at rewrites result in identical query plans. The optimizer does a good job here, but both of the single-trip queries is about 2x slower than the original.

In this case, that difference will look and feel microscopic. And it is, mostly because I provided indexes so good that you could write this query any which way and it would work out pretty well.

While one round trip certainly felt more efficient than three, each trip from outer to inner side of the nested loops had a bit more work to do each time, and that added up.

It’s nothing consequential here, but you may run into plenty of situations where it’s far worse (or to be fair, far better).

For me, the real advantage of writing out the three separate subqueries is to better understand which one(s) do the most work, and might need additional work done to make them fast.

When you do everything all at once, you have no idea which piece is responsible for slowdowns. We know from the very first query plan that getting the full count does the most work, but that wouldn’t be obvious to me, you, or anyone else looking at the two query plans in this section.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.