Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Question

Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

), --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

), --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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jeff Moden SSC Guru Points: 1004619 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Cookies on SQLServerCentral