the use of CTEs

Question

the use of CTEs

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Jeff Moden (1/8/2016)
Hugo Kornelis (1/8/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but the rather well advertised rave in SQL Server 2000 was being able to traverse hierarchies and the like using recursive functions that operated much like an rCTE. Before that, you could write recursive stored procedures. Yes, there were only 32 levels of recursion available but that's almost twice as many levels as you would need for a BOM for a 747 which, IIRC, only has something like 18 levels.
I never tried either myself because I didn't see the need at the time but the code was out there.
Did you miss the post where I promised anyone suggesting to replace recursive CTE with iterative code to be subjected to fish-slapping?
You might want to leave some of that popcorn for bystanders when we next meet. 😀
Why do you think I responded, Hugo? I'm going fishing soon and I need you and your fish for bait. 😉 Would you like to see the While Loop that will beat an rCTE?

I think you should...now that you've mentioned it.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

erics44 (1/8/2016)
ChrisM@Work (1/8/2016)
erics44 (1/8/2016)
ChrisM@Work (1/8/2016)
erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but the rather well advertised rave in SQL Server 2000 was being able to traverse hierarchies and the like using recursive functions that operated much like an rCTE. Before that, you could write recursive stored procedures. Yes, there were only 32 levels of recursion available but that's almost twice as many levels as you would need for a BOM for a 747 which, IIRC, only has something like 18 levels.
I never tried either myself because I didn't see the need at the time but the code was out there.
Did you miss the post where I promised anyone suggesting to replace recursive CTE with iterative code to be subjected to fish-slapping?
You might want to leave some of that popcorn for bystanders when we next meet. 😀
what if you cab prove you need more than 100 recursions? do you still receive the punishment?
You'll get an extra punishment for failing to use the MAXRECURSION keyword properly. :satisfied:
grrrrrr I knew there would be a smart alec
whats the max 35000?
what id you could prove that?
and would that be considered daft use of a recursive CTE?
Let's see what happens if we open the green door 😉
I like that 🙂
but how does it relate to what we were talking about?
It shows you usage of MAXRECURSION(0), unlimited recursions, which answers two of your questions above. An answer to the third question would be "row generation"; rCTE's are much slower than other conventional methods such as chained CTE's.
You learn something new 🙂
I honestly have never needed a recursive CTE that recurses more than a few times

Recursive CTE's are still a good solution when dealing with hierarchical data. E.g. When you need to Convert an Adjacency List to Nested Sets. [/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but the rather well advertised rave in SQL Server 2000 was being able to traverse hierarchies and the like using recursive functions that operated much like an rCTE. Before that, you could write recursive stored procedures. Yes, there were only 32 levels of recursion available but that's almost twice as many levels as you would need for a BOM for a 747 which, IIRC, only has something like 18 levels.
I never tried either myself because I didn't see the need at the time but the code was out there.

I forgot about those and have never written one. Something I want to play around with (strictly for fun) using a recursive stored procedure as a possible alternative to a recursive CTE for traversing hierarchies. The hierarchical data I'm working with now is 15 levels deep.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Kedazs (1/11/2016)
Yes. I also use CTE's to "clean up my code". Especially if it is something being called multiple times within the select. I do not think there is any real wrong way. That is the beauty of SQL server.

That's a perfect proof to my point why CTE's are bad.

Calling the same sub-query in a single "outer" query is not a good thing.

It's actually one of the top sources for performance issues.

Especially when it's in "SELECT" part of the query.

If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right.

Doing that with derived tables makes your code look ugly, giving you a hint that something is not right here.

CTE makes this horrible coding practice look so nice and innocent, encouraging you to continue with the wrong approach.

All performance issues go "under the carpet".

Until the carpet cannot cover them anymore.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Sergiy (1/11/2016)
Kedazs (1/11/2016)
Yes. I also use CTE's to "clean up my code". Especially if it is something being called multiple times within the select. I do not think there is any real wrong way. That is the beauty of SQL server.
That's a perfect proof to my point why CTE's are bad.
Calling the same sub-query in a single "outer" query is not a good thing.
It's actually one of the top sources for performance issues.
Especially when it's in "SELECT" part of the query.
If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right.
Doing that with derived tables makes your code look ugly, giving you a hint that something is not right here.
CTE makes this horrible coding practice look so nice and innocent, encouraging you to continue with the wrong approach.
All performance issues go "under the carpet".
Until the carpet cannot cover them anymore.

Just going to say it again, CTEs are a tool. Used correctly they are great, used incorrectly they can be very bad. Learn to use the tool correctly.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Lynn Pettis (1/11/2016)
Sergiy (1/11/2016)
Kedazs (1/11/2016)
Yes. I also use CTE's to "clean up my code". Especially if it is something being called multiple times within the select. I do not think there is any real wrong way. That is the beauty of SQL server.
That's a perfect proof to my point why CTE's are bad.
Calling the same sub-query in a single "outer" query is not a good thing.
It's actually one of the top sources for performance issues.
Especially when it's in "SELECT" part of the query.
If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right.
Doing that with derived tables makes your code look ugly, giving you a hint that something is not right here.
CTE makes this horrible coding practice look so nice and innocent, encouraging you to continue with the wrong approach.
All performance issues go "under the carpet".
Until the carpet cannot cover them anymore.
Just going to say it again, CTEs are a tool. Used correctly they are great, used incorrectly they can be very bad. Learn to use the tool correctly.

As you can see from the quoted post, for some reason people are especially happy to use CTE's in those ways which signify "use incorrectly".

Probably because "use correctly" means "stick to derived tables". :hehe:

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Alan.B (1/11/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but the rather well advertised rave in SQL Server 2000 was being able to traverse hierarchies and the like using recursive functions that operated much like an rCTE. Before that, you could write recursive stored procedures. Yes, there were only 32 levels of recursion available but that's almost twice as many levels as you would need for a BOM for a 747 which, IIRC, only has something like 18 levels.
I never tried either myself because I didn't see the need at the time but the code was out there.
I forgot about those and have never written one. Something I want to play around with (strictly for fun) using a recursive stored procedure as a possible alternative to a recursive CTE for traversing hierarchies. The hierarchical data I'm working with now is 15 levels deep.

How many nodes? And is it a clean Adjacency List you're working with or something else?

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

erics44 SSCertifiable Points: 6311 More actions · Answer 8

Lynn Pettis (1/11/2016)
Sergiy (1/11/2016)
Kedazs (1/11/2016)
Yes. I also use CTE's to "clean up my code". Especially if it is something being called multiple times within the select. I do not think there is any real wrong way. That is the beauty of SQL server.
That's a perfect proof to my point why CTE's are bad.
Calling the same sub-query in a single "outer" query is not a good thing.
It's actually one of the top sources for performance issues.
Especially when it's in "SELECT" part of the query.
If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right.
Doing that with derived tables makes your code look ugly, giving you a hint that something is not right here.
CTE makes this horrible coding practice look so nice and innocent, encouraging you to continue with the wrong approach.
All performance issues go "under the carpet".
Until the carpet cannot cover them anymore.
Just going to say it again, CTEs are a tool. Used correctly they are great, used incorrectly they can be very bad. Learn to use the tool correctly.

I suppose that's what the thread is about

how do you use CTEs correctly

I have an opinion

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

mister.magoo (1/11/2016)
Jeff Moden (1/8/2016)
Hugo Kornelis (1/8/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but the rather well advertised rave in SQL Server 2000 was being able to traverse hierarchies and the like using recursive functions that operated much like an rCTE. Before that, you could write recursive stored procedures. Yes, there were only 32 levels of recursion available but that's almost twice as many levels as you would need for a BOM for a 747 which, IIRC, only has something like 18 levels.
I never tried either myself because I didn't see the need at the time but the code was out there.
Did you miss the post where I promised anyone suggesting to replace recursive CTE with iterative code to be subjected to fish-slapping?
You might want to leave some of that popcorn for bystanders when we next meet. 😀
Why do you think I responded, Hugo? I'm going fishing soon and I need you and your fish for bait. 😉 Would you like to see the While Loop that will beat an rCTE?
I think you should...now that you've mentioned it.

NP, although I was waiting for Hugo to take the bait. 😉

Here's the code. Simplest example there is for an rCTE that I can think of. It's also the most frequently abused especially in articles supposedly about performance.

--===== rCTE (Recursive CTE) ==============================

SET NOCOUNT ON;

DECLARE @Bitbucket INT

;

WITH

cteCount AS

(SELECT N = 1

UNION ALL

SELECT N = N + 1

FROM cteCount

WHERE N < 1000000

)

SELECT @Bitbucket = N

FROM cteCount

OPTION (MAXRECURSION 0)

;

GO 5

--===== WHILE Loop Transaction ==============================

SET NOCOUNT ON;

CREATE TABLE #MyHead (N INT)

;

DECLARE @Counter INT

,@BitBucket INT;

SET @Counter = 1

;

BEGIN TRANSACTION;

WHILE @Counter <= 1000000

BEGIN

INSERT INTO #MyHead (N) VALUES (@Counter);

SET @Counter = @Counter + 1;

END;

COMMIT

;

SELECT @Bitbucket = N

FROM #MyHead

;

DROP TABLE #MyHead

;

GO 5

I used SQL Profiler and captured the output to a table, just to make life easy. Here's the code I used to return the data from the table...

SELECT TextData = SUBSTRING(TextData,1,37)

,DurationMS = Duration/1000

,CPU

,Reads

,Writes

,RowCounts

FROM rCTE_vs_WhileLoop

WHERE EventClass = 12

ORDER BY RowNumber

;

... and here are the results. While Loop beats the rCTE, in this case.

TextData DurationMS CPU Reads Writes RowCounts

------------------------------------- ---------- ---- ------- ------ ---------

--===== rCTE (Recursive CTE) ======== 7488 6770 9000001 0 1000000

--===== rCTE (Recursive CTE) ======== 7431 7067 9000001 0 1000000

--===== rCTE (Recursive CTE) ======== 7334 6942 9000001 0 1000000

--===== rCTE (Recursive CTE) ======== 7469 7113 9000001 0 1000000

--===== rCTE (Recursive CTE) ======== 7443 6974 9000001 0 1000000

--===== WHILE Loop Transaction ====== 6894 6848 1016244 6 3000001

--===== WHILE Loop Transaction ====== 7025 6864 1016238 11 3000001

--===== WHILE Loop Transaction ====== 6912 6911 1016235 6 3000001

--===== WHILE Loop Transaction ====== 6928 6848 1016238 0 3000001

--===== WHILE Loop Transaction ====== 6949 6896 1016241 0 3000001

For a short hierarchy traversal, the rCTE edges out the While Loop when the correct indexes are in place. With no indexes, the rCTE wins by a fair amount. Haven't tried a race against a larger set (million rows would be a good test), yet, although I suspect logical reads might really be important there.

Just a note... I did dump the output of both to a variable to take display time out of the picture. In fairness to the rCTE, I did make the While Loop collect the whole set by writing it to a Temp Table and then selected from the Temp Table.

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

Kedazs SSC Veteran Points: 267 More actions · Answer 10

Kedazs

SSC Veteran

Points: 267

January 12, 2016 at 7:40 am

#1851310

2

Kedazs SSC Veteran Points: 267 More actions · Answer 11

"If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right."

Wow. Great help and advice. Thought we were here to teach and help each other out.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Kedazs (1/12/2016)
"If you do it more than once in a year - you need to rethink you approach to T-SQL programming, and probably take some lessons on how to do it right."
Wow. Great help and advice. Thought we were here to teach and help each other out.

Actually, that was an honest advice.

No sarcasm or anything like that attempted.

Yes, once in a year you may feel like letting repeated blocks of code into your query. But if it becomes a habit - change your ways.

We can't be a replacement for T-SQL programming courses, but if you post your problem we could show you a better approach to its solution.

_____________
Code for TallyGenerator