the use of CTEs

Question

the use of CTEs

Viewing 15 posts - 136 through 150 (of 161 total)

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

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

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.

--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 SSC Guru Points: 1004363 More actions · Answer 2

Hugo Kornelis (1/6/2016)
Oh, one more note. In between all the getting carried away with details and terminology and everyone being right and stuff, I forgot to add my two cents about the original question, which (if I remember correctly) was about over-use of CTEs.
My take on that is that any feature in T-SQL, or in fact any feature in any language, should be used where useful and not used where not. Overuse of any feature is always bad.

Finally. Someone got back to the original subject. +1000 to that and the notion that overuse of a feature is bad. I guess folks are just trying to hammer out what the word "overuse" could be, in this case. For a lot of people, it's going to be highly subjective varying from any use being over use to always using it for everything. Then there are those that have touched on the "improper use" of CTEs, which could be categorized as "over use".

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

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

Jason A. Long (1/7/2016)

...
Edit: As a side note... When I included TVFs in that statement, I was referring to the "parameterized view" type of TVF... Not a TVF that's added at the row level with an APPLY operator... I guess we could call that a "correlated sub-query" type of TVF???

Can you demonstrate these two kinds of TVF with code?

Assuming you're referring to inline TVF's, where do multi-statement TVF's fit into this?

iTVF's, CTE's, subqueries and derived tables are almost always just subbed into the plan in whichever way the optimiser sees fit. Join two tables in a CTE and join the product to a third table - in code - and there's absolutely no guarantee that the optimiser will choose a plan to reflect the join pattern (without the obvious hints of course).

^{“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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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. 😀

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

erics44 SSCertifiable Points: 6311 More actions · Answer 5

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?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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:

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

erics44 SSCertifiable Points: 6311 More actions · Answer 7

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?

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

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 😉

^{“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

erics44 SSCertifiable Points: 6311 More actions · Answer 9

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?

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

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.

^{“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

erics44 SSCertifiable Points: 6311 More actions · Answer 11

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

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

erics44 (1/8/2016)
...and would that be considered daft use of a recursive CTE?

Virtually any form of counting or other RBAR, even if it's for a low number of iterations. See the following.

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)

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

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?

--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 SSC Guru Points: 1004363 More actions · Answer 14

Jason A. Long (1/7/2016)
Jeff Moden (1/7/2016)
Can't argue that without an example. I'll have to check on some of the execution plans on my prod system. I'm pretty sure I've seen where the underlying tables were not the rightmost tables in the execution plan. I'm normally concerned with the overall performance than whose on stage when so don't have such an example off the top of my head. I'm also not agreeing with you yet because you can't argue that without an example, either. 😀
Well said and all fair points... This quick, little test actually appears to throw it into your favorite category... "IT DEPENDS"
USE AdventureWorks;
GO
SELECT
CustomerID = p.BusinessEntityID,
CustomerName = CONCAT(p.FirstName + ' ', p.MiddleName + '. ', p.LastName),
so.OrderCount,
so.OrderQty,
so.LineTotal
FROM
Person.Person p
JOIN (
SELECT
soh.CustomerID,
OrderCount = COUNT(*),
OrderQty = SUM(sd.OrderQty),
LineTotal = SUM(sd.LineTotal)
FROM
Sales.SalesOrderHeader soh
JOIN (
SELECT
sod.SalesOrderID,
OrderQty = SUM(sod.OrderQty),
LineTotal = SUM(sod.LineTotal)
FROM
Sales.SalesOrderDetail sod
GROUP BY
sod.SalesOrderID
) sd
ON soh.SalesOrderID = sd.SalesOrderID
GROUP BY
soh.CustomerID
) so
ON p.BusinessEntityID = so.CustomerID
--WHERE
--p.BusinessEntityID = 12079
;
Without the WHERE clause, all 3 tables are being scanned and all aggregation operators are taking place before the join operators... Indicating that the inner most derived tables are being evaluated first.
But... Add the where clause (predicated on the PK of the outermost table) and that's no longer the case... All 3 tables have index seeks and the details & header table are joined before the aggregations...
This indicates that SQL Server is smart enough to recognize that it can use the outer query predicate to avoid the unnecessary work rolling up values that will be discarded by the outer query.
Not sure if this falls under the category of "logical" or "physical" order of operations... I just know the distinction isn't a hill worth dying over...
Call it a draw? 😀

Maybe. I'll have to check it out. Thanks for your time, Jason. Furnace guys are going to be here any minute to replace the furnace so I'll have to wait to do a deep dive on your good efforts.

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

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.