Performance Tuning
How Much Can One Row Change A Plan, Part 3
All The Rage
We’ve asked how much one column and even one row can change a plan before.
The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received.
So here we go again, ONCE MORE INTO THE FRAY!
Or is it ONCE MORE UNTO THE BREACH?
Eh, books.
Let’s SQL
Here’s contestant number one:
|
1 2 3 4 5 6 |
SELECT TOP 3 c.Id FROM dbo.Comments AS c WHERE NOT EXISTS ( SELECT * FROM dbo.Votes AS v WHERE c.PostId = v.PostId ) ORDER BY c.CreationDate DESC; |
And the results from stats time and IO:
|
1 2 3 4 5 6 7 |
Table 'Comments'. Scan count 7, logical reads 159138, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Votes'. Scan count 7, logical reads 287662, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 88672 ms, elapsed time = 16255 ms. |
Here’s contestant number two:
|
1 2 3 4 5 6 |
SELECT TOP 2 c.Id FROM dbo.Comments AS c WHERE NOT EXISTS ( SELECT * FROM dbo.Votes AS v WHERE c.PostId = v.PostId ) ORDER BY c.CreationDate DESC; |
And the results from stats time and IO:
|
1 2 3 4 5 |
Table 'Votes'. Scan count 1, logical reads 1375434, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Comments'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 21156 ms, elapsed time = 21150 ms. |
Tally up
So uh
I’ll follow up when I finish my math degree.
Or later this week.
Thanks for reading!
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Or, 1 row too many anywhere in the work table results (sort runs, etc.) can overflow memory and cause a spill?
Stay tuned!
[…] How Much Can One Row Change A Plan, Part 3 (Erik Darling) […]