March 22, 2013 at 10:11 am
Lynn Pettis (3/22/2013)
Megha P (3/22/2013)
Hello ,you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)
SELECT * FROM CTE ORDER BY docid,Pages
Your recursive CTE will be slower and won't scale as well as the straight tally method.
It's a clever little rCTE so let's have a look:
Code:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c)
SELECT
docid = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
Pages = (ABS(CHECKSUM(NEWID()))%4) + 1
INTO #X
FROM E4;
CREATE UNIQUE CLUSTERED INDEX ucx_docid ON #x (docid)
UPDATE STATISTICS #x WITH FULLSCAN
DECLARE @docid INT, @pages INT
PRINT ''
PRINT 'Q1 ================================================================================='
SET STATISTICS TIME, IO ON;
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c),
Tally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) from E4)
SELECT
x.docid,
y.pages
INTO #Q1
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = n FROM Tally) y
ORDER BY x.docid, y.Pages;
SET STATISTICS TIME, IO OFF;
PRINT ''
PRINT 'Q2 ================================================================================='
SET STATISTICS TIME, IO ON;
SELECT
x.docid,
y.pages
INTO #Q2
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = ROW_NUMBER() OVER(ORDER BY docid) FROM #X) y
ORDER BY x.docid, y.Pages;
SET STATISTICS TIME, IO OFF;
PRINT ''
PRINT 'Q3 ================================================================================='
SET STATISTICS TIME, IO ON;
WITH CTE AS
(
SELECT
docid, pages
FROM #x
UNION ALL
SELECT
C.docid, C.pages-1
FROM CTE C
INNER JOIN #x X
ON X.docid = C.docid
AND C.pages-1 > 0
)
SELECT
docid,
Pages
INTO #Q3
FROM CTE
ORDER BY docid,Pages;
SET STATISTICS TIME, IO OFF;
PRINT ''
PRINT '================================================================================='
Results:
Q1 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 2608, 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 = 1732 ms, elapsed time = 1966 ms.
(2499713 row(s) affected)
Q2 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 2, logical reads 3002608, 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 = 2605 ms, elapsed time = 3546 ms.
(2499713 row(s) affected)
Q3 =================================================================================
Table 'Worktable'. Scan count 2, logical reads 19497919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 4501746, 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 = 36036 ms, elapsed time = 40989 ms.
(2499713 row(s) affected)
=================================================================================
The rCTE is a lot slower.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2013 at 7:40 pm
Megha P (3/22/2013)
Hello ,you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)
SELECT * FROM CTE ORDER BY docid,Pages
I'll throw in with the others on that. That's what is know as a "Counting rCTE". Another name for it is "Hidden RBAR". Please see the following article for a comparison of rCTEs to 3 other common methods. The rCTE loses on all fronts.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy