May 17, 2008 at 7:16 am
Comments posted to this topic are about the item Database Weekly Update for May 19, 2008
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 17, 2008 at 9:36 am
There are many people that don't really understand how recursion works, and with it's new possibilities with CTEs, I'd really encourage all DBAs to add this tool to their list of techniques to consider. It can be tricky, but there's a great blog post from Tony Rogerson, SQL Server MVP in the UK that you should read.
The operative words are "There are many people that don't really understand how recursion works"... most don't realize that recursion is a hidden form of RBAR and, like a cursor or While loop, should only be used as a last resort, especially in batch code.
--Jeff Moden
May 17, 2008 at 7:54 pm
Jeff and his RBAR 😉
Learn how recursion works. Even if you don't use it in SQL Server, it's a good tool to understand.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 17, 2008 at 8:39 pm
Heh... 😛
Just a simple demonstration to show just how slow recursion can be. Let's populate a table with the number of 1 to 100,000... first method uses recursion... second method uses a set based method. See which is faster and which uses fewer resources...
SET NOCOUNT OFF
--=============================================================================
-- Recursive CTE does the count
--=============================================================================
PRINT '========== Recursive CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT 1 AS N
UNION ALL
SELECT N+1 FROM cteTally WHERE N<@Top
)
SELECT N
INTO #Test1
FROM cteTally
OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
--=============================================================================
-- ROW_NUMBER CTE does the count
--=============================================================================
PRINT '========== ROW_NUMBER CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N
FROM Master.dbo.spt_Values sc1,
Master.dbo.spt_Values sc2
)
SELECT *
INTO #Test2
FROM cteTally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
DROP TABLE #Test1,#Test2
Here's the results on my humble 1.8Mhz desktop server...
[font="Courier New"]========== Recursive CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, 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 = 3781 ms, elapsed time = 4611 ms.
(100000 row(s) affected)
====================================================================================================
========== ROW_NUMBER CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'spt_values'. Scan count 2, logical reads 18, 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 = 141 ms, elapsed time = 159 ms.
(100000 row(s) affected)
====================================================================================================[/font]
I do agree with Steve though... learn everything you can about recursion... so you can figure out a way to NOT use it 😉
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply