The Fastest Ways To Get The Highest Value In SQL Server Part 3

Silent and Grey


In yesterday’s post, we looked at plans with a good index. The row number queries were unfortunate, but the MAX and TOP 1 queries did really well.

Today, I wanna see what the future holds. I’m gonna test stuff out on SQL Server 2019 CTP 3.1 and see how things go.

I’m only going to hit the interesting points. If plans don’t change, I’m not gonna go into them.

Query #1

With no indexes, this query positively RIPS. Batch Mode For Row Store kicks in, and this finishes in 2 seconds.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT MAX(Score) AS Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;
SQL Server Query Plan
So many illustrations

With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.

No big surprise there. Not worth the picture.

Query #2


Our TOP 1 query should be BOTTOM 1 here. It goes back to its index spooling ways, and runs for a minute.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT TOP (1) p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
	ORDER BY p.Score DESC

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.

No big surprise there. Not worth the picture.

I feel like I’m repeating myself.

Query #3

This is our first attempt at row number. It’s particularly disappointing when we see the next query plan.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT p.Score,
	       ROW_NUMBER() OVER (ORDER BY p.Score DESC) AS n
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
) AS ca
WHERE u.Reputation >= 100000
AND ca.n = 1
ORDER BY u.Id;

On its own, it’s just regular disappointing.

SQL Server Query Plan
Forget Me

Serial. Spool. 57 seconds.

With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.

No big surprise there. Not worth the picture.

I feel like I’m repeating myself.

Myself.

Query #4

Why this plan is cool, and why it makes the previous plans very disappointing, is because we get a Batch Mode Window Aggregate.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT * 
	FROM 
	(
        SELECT p.OwnerUserId,
	           p.Score,
	           ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId 
			                      ORDER BY p.Score DESC) AS n
	    FROM dbo.Posts AS p
	) AS p
	WHERE p.OwnerUserId = u.Id
	AND p.n = 1
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;
SQL Server Query Plans
Guillotine

It finishes in 1.7 seconds. This is nice. Good job, 2019.

With the index we get a serial Batch Mode plan, which finishes in about 1.4 seconds.

SQL Server Query Plans
Confused.

If you’re confused about where 1.4 seconds come from, watch this video.

Why Aren’t You Out Yet?


SQL Server 2019 did some interesting things, here.

In some cases, it made fast queries faster.

In other cases, queries stayed… exactly the same.

When Batch Mode kicks in, you may find queries like this speeding up. But when it doesn’t, you may find yourself having to do some good ol’ fashion query and index tuning.

No big surprise there. Not worth the picture.

I feel like I’m repeating myself.

Myself.

Myself.

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.