Le Big Zoom Zoom
When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you want.
Originally introduced to accompany column store indexes, it works by allowing CPUs to apply instructions to up to 900 rows at a time.
It’s a great thing to have in your corner when you’re tuning queries that do a lot of work, especially if you find yourself dealing with pesky parallel exchanges.
One way to get that to happen is to use a temp table with a column store index on it.
SELECT v.UserId, SUM(v.BountyAmount) AS SumBounty FROM dbo.Comments AS c JOIN dbo.Votes AS v ON v.PostId = c.PostId AND v.UserId = c.UserId GROUP BY v.UserId ORDER BY SumBounty DESC; CREATE TABLE #t(id INT, INDEX c CLUSTERED COLUMNSTORE); SELECT v.UserId, SUM(v.BountyAmount) AS SumBounty FROM dbo.Comments AS c JOIN dbo.Votes AS v ON v.PostId = c.PostId AND v.UserId = c.UserId LEFT JOIN #t AS t ON 1 = 0 GROUP BY v.UserId ORDER BY SumBounty DESC;
Keep in mind, this trick won’t work if you’re on SQL Server 2019 and using in memory tempdb. But aside from that, you’re free to rock and roll with it.
If you end up using this enough, you may just wanna create a real table to use, anyway.
If we look at the end (or beginning, depending on how you read your query plans) just to see the final times, there’s a pretty solid difference.
The first query takes around 10 seconds, and the second query takes around 4 seconds. That’s a pretty handsome improvement without touching anything else.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
One thought on “Making The Most Of Temp Tables Part 4: Batch Mode”
Comments are closed.