Trick Shots
Query tuning is hard work, especially when queries run for a long time and you have to test various changes trying to get it down to a reasonable runtime.
There are, of course, things you pick up intuitively over time, and figuring out where problems in actual execution plans has gotten easier with operator runtimes.
Beyond basic query tuning intuition comes the really creative stuff. The stuff you’re amazed anyone ever thought of.
I’m going to show you some examples of that.
Cross Tops
Let’s say you want to find the top 1000 rows for a few whatevers. In my case, whatever are users, and the table is Posts.
We’ll start by creating an index to support our query:
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId, CreationDate DESC)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
The most intuitive way to write the query looks like this:
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE p.OwnerUserId IN (22656, 29407, 157882)
ORDER BY
p.CreationDate DESC;
Because of my super professional indexing skills, the query plan looks like this:
Is this good? Bad? Good enough? Well, it takes 328ms, and asks for memory to sort data. It’s a little bit weird that we need to sort anything, because our index has CreationDate in order, and we’re doing what would appear to be equality searches on OwnerUserId.
One query pattern I’ve seen and written copied on multiple occasions is something like this:
SELECT TOP (1000)
p.*
FROM
(
VALUES
(22656),
(29407),
(157882)
) AS x (x)
CROSS APPLY
(
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE p.OwnerUserId = x.x
ORDER BY p.CreationDate DESC
) AS p
ORDER BY
p.CreationDate DESC;
It’s a little more complicated, but we use the VALUES clause to list our literals, and CROSS APPLY them with the Posts table.
Is it better? Best? Finally good enough?
Well, it finishes a lot faster, but it still results in a sort, and a ~3x increase in the memory grant, up to 1GB. A fascinating conundrum.
Unionville
A query pattern that really baked my noodle like stoner casserole is this one:
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE p.OwnerUserId IN (SELECT 22656 UNION SELECT 29407 UNION SELECT 157882)
ORDER BY
p.CreationDate DESC;
Rather than just listing values by comma, or using the VALUES clause, we’re selecting each value UNIONed (not UNION ALLed) in the IN clause.
How do we do here? Good? Done? Finally?
Well, the query plan is a hell of a lot bigger, but it finishes and doesn’t ask for any memory. The order is preserved by Merge Concatenation.
Like I said: casserole.
Paster Blaster
One of the very first demos I saw that made me feel totally out of my depth in T-SQL is this one.
Seriously. Watch the video to get a full explanation for the query and why it’s written this way.
Because the code is hard to read and the video is sort of blurry, I’m going to reproduce it here using the same basic idea in the Stack Overflow database:
First, some supportive indexes:
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId)
INCLUDE
(Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
c
ON dbo.Comments
(UserId)
INCLUDE
(Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
And now, the reproduced query:
SELECT TOP (1000)
u.DisplayName,
ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
SELECT
Score = SUM(x.Score)
FROM
(
SELECT
c.Score
FROM dbo.Comments AS c
WHERE c.UserId = u.Id
UNION ALL
SELECT
p.*
FROM
(
SELECT TOP (1)
f = 1
WHERE u.CreationDate >= '2016-01-01'
AND u.Reputation = 10
) AS f
CROSS APPLY
(
SELECT
p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
) AS p
) AS x
) AS ca
ORDER BY
u.Reputation DESC;
And the reproduced query plan:
Seriously. There’s a SELECT TOP (1) with no FROM clause, and a WHERE clause.
I can’t imagine how much alcohol it would take to figure that out.
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.