|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 1:59 AM
Points: 358,
Visits: 809
|
|
Paul Paiva (2/12/2009)
Excellent and clearly written!
I'm always reading CTE articles and this is one of the best, because it explains a situation which is very commonly encountered.
- Paul
Thanks, Paul. I'm glad you liked it!
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 1:59 AM
Points: 358,
Visits: 809
|
|
mishaluba (1/29/2009)
Absolutely loved the article. I am sure everyone had to do something like this at one time or another. Thank you for sharing this technique and nice explanations.
My pleasure - thank you for your kind words.
David.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, March 18, 2010 1:21 PM
Points: 114,
Visits: 186
|
|
Very helpful article. Thank you.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 2,094,
Visits: 4,418
|
|
peter-757102 (1/23/2009)
I understand your point of view as well as the theory you put forward as to why it might be different in case of my proof. Therefore I did the query once more on my modified view to see if the statistics and query plan where any different from the original. They were not and that is 100% proof that the original also generates no intermediate results.
The reasons I feel it is important to change:
1. Half the discussion goes about using CTEs and you confirmed it is one of the key ingredients that you build your case upon and is in fact a focus.
2. The article is very easy to correct without negative effects.
If I am right and I think I am, people new to CTE get put on the wrong track from the start. When they think a CTE is a means to get an intermediate result they will code with that in mind. Also, this forum is about learning and sharing knowledge, so it is important the information is as correct as possible. Therefore I hope you will reconsidder after taking a look at the code I posted. There is a case to be made to keep the article as is and add a correction at the bottom or top. This way the following discussion keeps making sense for new readers.
Other then that, enjoy your weekend....I sure will :)
Nice work, Peter, but I'm curious - if a CTE doesn't generate any kind of intermediate result, then how might a recursive CTE work? Bear in mind that for each recursion, a different rowset from the "intermediate result" may be exposed to the source table. I don't think this necessarily clashes with your evidence that CTE's could be views by another name.
Cheers
ChrisM
Low-hanging fruit picker
For better assistance in answering your questions, please read this.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 19, 2010 4:26 AM
Points: 128,
Visits: 929
|
|
Chris Morris-439714 (2/8/2010)
peter-757102 (1/23/2009)
I understand your point of view as well as the theory you put forward as to why it might be different in case of my proof. Therefore I did the query once more on my modified view to see if the statistics and query plan where any different from the original. They were not and that is 100% proof that the original also generates no intermediate results.
The reasons I feel it is important to change:
1. Half the discussion goes about using CTEs and you confirmed it is one of the key ingredients that you build your case upon and is in fact a focus.
2. The article is very easy to correct without negative effects.
If I am right and I think I am, people new to CTE get put on the wrong track from the start. When they think a CTE is a means to get an intermediate result they will code with that in mind. Also, this forum is about learning and sharing knowledge, so it is important the information is as correct as possible. Therefore I hope you will reconsidder after taking a look at the code I posted. There is a case to be made to keep the article as is and add a correction at the bottom or top. This way the following discussion keeps making sense for new readers.
Other then that, enjoy your weekend....I sure will :)Nice work, Peter, but I'm curious - if a CTE doesn't generate any kind of intermediate result, then how might a recursive CTE work? Bear in mind that for each recursion, a different rowset from the "intermediate result" may be exposed to the source table. I don't think this necessarily clashes with your evidence that CTE's could be views by another name. Cheers ChrisM
As with any query, with or without views and/or CTEs, SQL Sever uses RAM for intermediate results and falls back to tempdb as needed (similar to table variables and derived tables). For small sets of data even complex constructs such as 3 nested derived feel very fast. I never did formal testing on this however, the situations I used this in just performed way better as I would expect with storage taking place.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 2,094,
Visits: 4,418
|
|
peter-757102 (2/8/2010)
Chris Morris-439714 (2/8/2010)
peter-757102 (1/23/2009)
Nice work, Peter, but I'm curious - if a CTE doesn't generate any kind of intermediate result, then how might a recursive CTE work? Bear in mind that for each recursion, a different rowset from the "intermediate result" may be exposed to the source table. I don't think this necessarily clashes with your evidence that CTE's could be views by another name. Cheers ChrisM As with any query, with or without views and/or CTEs, SQL Sever uses RAM for intermediate results and falls back to tempdb as needed (similar to table variables and derived tables). For small sets of data even complex constructs such as 3 nested derived feel very fast. I never did formal testing on this however, the situations I used this in just performed way better as I would expect with storage taking place.
So you are still saying that it's incorrect to refer to an intermediate result set from a CTE? The reason I'm asking, to be clear, is because I don't know what to call the result set which is returned by a CTE in a recursive step, when each recursive call is quite likely to return a different result from the CTE.
Low-hanging fruit picker
For better assistance in answering your questions, please read this.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 19, 2010 4:26 AM
Points: 128,
Visits: 929
|
|
Look at the recursive part as just as another expression that is transplanted everywhere it is being used. Just as would be the case if the CTE would be located in a view that is used by a query. It is a functional description of what you want to do, no different from a select, an update or any SQL statement or expression and thus treated identical.
The optimizing process first transplants any view/CTE in the statement as if it is fully written out, then optimize the statement itself and then execute according to the compiled plan. Note that during compilation, optimizations might result in different execution plans for the same CTE if that CTE is used different in different parts of the consuming query (just as a view would).
For a demonstration, any condition that changes the effective output of the CTE in the spot where it is used should do the trick. Like a top 1 in a select or when you use a field generated in the CTE by a sub select that you do not use in one spot, but do in the other. Such cases should become visible as different operations in the query plan.
|
|
|
|
