How Much Can One Row Change A Plan, Part 3

Last Updated 6 years ago

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:

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;

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:

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.

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:

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;

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:

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.

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

Trade offs

So uh

I’ll follow up when I finish my math degree.

Or later this week.

Thanks for reading!

[Video] The Junior DBA Workout Plan

How Much Can One Row Change A Plan, Part 4

3 Comments. Leave new

ken
January 18, 2018 12:50 pm

Or, 1 row too many anywhere in the work table results (sort runs, etc.) can overflow memory and cause a spill?

Reply
- Erik Darling
  January 18, 2018 12:58 pm
  
  Stay tuned!
  
  Reply
Dew Drop - January 19, 2018 (#2647) - Morning Dew
January 19, 2018 6:23 am

[…] How Much Can One Row Change A Plan, Part 3 (Erik Darling) […]

Reply