avoding recursion in CTE
-
Is there is any way to break / avoid the recursion in the CTE QUERY??
Abhijit - http://abhijitmore.wordpress.com
-
CTE does not mean always a recursive query. But it is very powerful when recursive queries are required.
If your application require recursive process, then CTE is the first tool to use.
But if you can avoid somehow, but not by creating temp tables, writing cursors, etc you can stay away.
But if you are using cursors instead of CTEs, then that will not be a performance optimized option.
-
Abhijit More (12/2/2010)
Is there is any way to break / avoid the recursion in the CTE QUERY??Break? What do you mean?
Avoid? Yes of course - don't write your CTE as a recursive one. A recursive CTE refers to itself, like this:
;WITH Calculator (n, CalcValue) AS (SELECT n.n, CalcValue = CAST(n.n AS BIGINT)
FROM #Numbers n
WHERE n.n = 1
UNION ALL
SELECT n.n, CalcValue = n.n + c.n
FROM #Numbers n
INNER JOIN Calculator c ON c.n + 1 = n.n
)
SELECT n, CalcValue
FROM Calculator
WHERE n = 3063
OPTION (MAXRECURSION 0)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Eralper (12/2/2010)
But if you are using cursors instead of CTEs, then that will not be a performance optimized option.In most cases of recursive CTE's, I've actually found the WHILE loop to be faster and much more gentle on the number of reads... at least in 2K5. I've not tested it in 2K8 yet. Where something may take a 1,000 reads in a WHILE loop, it can take 3,000,000 reads in a recursive CTE. Yeah, I know... most of the reads are logical reads but even that many logical reads matter.
--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) -
Abhijit More (12/2/2010)
Is there is any way to break / avoid the recursion in the CTE QUERY??No. If you could explain a bit more of what you're actually trying to do, we can show you at least one good way of doing it.
--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) -
Jeff Moden (12/11/2010)
Eralper (12/2/2010)
But if you are using cursors instead of CTEs, then that will not be a performance optimized option.In most cases of recursive CTE's, I've actually found the WHILE loop to be faster and much more gentle on the number of reads... at least in 2K5. I've not tested it in 2K8 yet. Where something may take a 1,000 reads in a WHILE loop, it can take 3,000,000 reads in a recursive CTE. Yeah, I know... most of the reads are logical reads but even that many logical reads matter.
Sorry, I don't normally post something like that without proof... here's the proof. First, the test code (a conglomeration of people's code)...
--===== Drop Tables ===========================================================================DROP TABLE #Number, #Number1, #Number2, #Tally, #Tally1;
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
--===== Recursive CTE w/Predefined Table (original)
create table #Number (number int);
begin transaction;
with Nx as (select 1 as Number union all select Number+1 from Nx where Number <1000000
)insert into #number select * from Nx option (MAXRECURSION 0);
GO
--===== Recursive CTE w/SELECT/INTO
with Nx as (select ISNULL(1,0) as Number union all select Number+1 from Nx where Number <1000000
)select * INTO #Number1 from Nx option (MAXRECURSION 0);
GO
--===== WHILE Loop
CREATE TABLE #Number2 (Number INT NOT NULL);
DECLARE @Counter INT;
SELECT @Counter = 1;
BEGIN TRANSACTION;
WHILE @Counter <= 1000000
BEGIN
INSERT INTO #Number2
(Number)
SELECT @Counter;
SET @Counter = @Counter + 1;
END;
COMMIT;
GO
--===== Cross Join
SELECT TOP 1000000
N = IDENTITY(INT,1,1)
INTO #Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Modified Itzek Method
WITH
E1(N) AS ( --=== Create Ten 1's
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 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E8)
SELECT N = ISNULL(N,0)
INTO #Tally1
FROM cteTally
WHERE N <= 1000000
;
And here're the results from profiler on my 8 year old, single CPU box...
--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)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply