Last active
January 12, 2016 14:22
-
-
Save nathan-russell/0aa4ed76c0e67adc9165 to your computer and use it in GitHub Desktop.
Numbers Table vs. Recursive CTE
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE bizdev | |
-- | |
GO | |
-- | |
SET STATISTICS IO ON; | |
SET STATISTICS TIME ON; | |
-- | |
DECLARE @max INT = 40000; | |
-- | |
SELECT GETDATE() - n.Number | |
FROM dbo.Numbers n | |
WHERE n.Number <= @max; | |
-- | |
/* | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 2 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
(40001 row(s) affected) | |
Table 'Numbers'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. | |
^^^^^^^^^^^^^^^ | |
(1 row(s) affected) | |
SQL Server Execution Times: | |
CPU time = 16 ms, elapsed time = 120 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE bizdev | |
-- | |
GO | |
-- | |
SET STATISTICS IO ON; | |
SET STATISTICS TIME ON; | |
-- | |
DECLARE @max INT = 40000; | |
-- | |
WITH cte | |
AS ( SELECT 0 AS num | |
UNION ALL | |
SELECT ( num + 1 ) AS num | |
FROM cte | |
WHERE num < @max | |
) | |
SELECT GETDATE() - c.num | |
FROM cte c | |
OPTION ( MAXRECURSION 0 ); | |
-- | |
/* | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 1 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
(40001 row(s) affected) | |
Table 'Worktable'. Scan count 2, logical reads 240007, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. | |
^^^^^^^^^^^^^^^^^^^^ | |
(1 row(s) affected) | |
SQL Server Execution Times: | |
CPU time = 547 ms, elapsed time = 620 ms. | |
SQL Server parse and compile time: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
SQL Server Execution Times: | |
CPU time = 0 ms, elapsed time = 0 ms. | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Numbers Table Execution Plan
Recursive CTE Execution Plan