Starting with "WITH"

Question

Post reply

Starting with "WITH"

Edward-445599

Hall of Fame

Points: 3147
More actions
November 3, 2011 at 4:17 am

#246175

I have recently seen a few T-SQL queries starting with “WITH” I understanding what’s happening I am just surprised and a little embarrassed I haven’t seen this before, I have failed to get any documentation explaining this T-SQL, has any one got any link’s explaining this concept in detail (I don’t need the example explained I understand it)?
WITH example(example_ID) AS
(Select Min(ID) FROM Example_table GROUP BY example_ID,type)
Select *from table2 T2
Inner join example on T2.id = example.example_ID

Viewing 15 posts - 1 through 15 (of 55 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

this is a CTE declaration

http://msdn.microsoft.com/en-us/library/ms190766.aspx

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 2

Edward-445599

Hall of Fame

Points: 3147

November 3, 2011 at 4:33 am

#1402567

THANKS 😀

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 3

They also become very useful as they can work recusively.

http://msdn.microsoft.com/en-us/library/ms186243.aspx

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Recursive CTEs can also perform quite suckishly. There is nothing set-based about the recursion.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

RowanCollum SSC Veteran Points: 224 More actions · Answer 5

Suckishly? ¿Por qué? I've seen otherwise, but, I'm curious to know the instances where CTEs have performed poorly. Are you working in a data warehouse environment? Using the MAXRECURSION option?

I haven't encountered any CTE limitations, yet, but would like to be aware of any future issues.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Yes recursive CTEs can be slow - you need to be sure you have defined your recursion well enough.

I have seen them perform marvelously as well. For instance we were able to take a tree recursion down from 30 minutes using a loop method to under 30 seconds for hierarchies of 700+ levels and 1.5 million people.

There is also potential problems for introducing circular references with a recursive cte that are easier to avoid in a while loop or cursor so you have to watch for that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 7

The Dixie Flatline (11/3/2011)
Recursive CTEs can also perform quite suckishly. There is nothing set-based about the recursion.

There's nothing in recursion that prevents set-based description. The fact that we use hardware with at most a trivial degree of parellism prevents any real set-based operation, as opposed to description.

It's also plain that recursive CTEs could be optimised by replacing tail-recursion by iteration, just as the optimser/data-engine replaces every relational operation involving multiple rows by iteration, if in fact such optimisation is needed and is not already done (which would surprise me).

The most obvious thing you have missed is that some recursive CTEs are slow because they are doing something hard - and without them you would end up with an even slower solution, probably involving explicit loops and cursors.

The idea that one should avoid recursive CTEs in cases which can be handled as a single query without recursive CTEs is sensible; but dismissing them as not being set-based is just nonsense.

Tom

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

RowanCollum (11/3/2011)
Suckishly? ¿Por qué? I've seen otherwise, but, I'm curious to know the instances where CTEs have performed poorly.

Coming right up! 😉 Please see the article at the following link.

http://www.sqlservercentral.com/articles/T-SQL/74118/

All recursive CTEs (rCTEs) operate in a similar fashion. The ones to expand hierarchies aren't quite as bad because they do more than one row at a time but rCTEs are pretty tough on resource usage especially in the area of reads. They also make sucking sounds when it comes to performance. 😀

--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: 1004356 More actions · Answer 9

L' Eomot Inversé (11/3/2011)
There's nothing in recursion that prevents set-based description.

If you take a look at the "Reads" chart in the article I provided a link for in my previous post, you might just change your mind insofar as rCTEs go. They actually build a work table in Temp DB and cycle through it over and over and over and... RBAR on steroids.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Jeff Moden (11/4/2011)
L' Eomot Inversé (11/3/2011)
There's nothing in recursion that prevents set-based description.
If you take a look at the "Reads" chart in the article I provided a link for in my previous post, you might just change your mind insofar as rCTEs go. They actually build a work table in Temp DB and cycle through it over and over and over and... RBAR on steroids.

Looking again at the article wouldn't change my views. Counting with a recursive CTE is a silly idea (the article does an excellent job of pointing that out and showing people that other ways of counting perform much better). But not using a rCTE to do counting comes under my idea that an rCTE should not be used if it's possible to write a single SQL statement that doesn't use an rCTE to do the same job as the SQL statement that uses the rCTE.

That rCTEs are bad for counting doesn't make all use of rCTEs silly and it certainly doesn't demonstrate that rCTEs are not a set-based concept; would you claim that JOIN is not a set based concept just because it is possible to write silly triangular joins with appalling performance?

rCTEs work well to help solve some problems - for example getting aggregates over all descendants of a particular node in a generalised tree, or for finding paths in a colored directed network with weighted edges (finding shortest path, shortest path with minimal color changes, least weight path, shortest spanning path, spanning path with fewest colors, and so on).

Take the example Jason reported a few posts above - rCTEs gave him a factor of 60 performance improvement over the alternative. Sounds as if they were less RBAR than the alternative in that case.

Tom

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

SQLRNNR (11/3/2011)
Yes recursive CTEs can be slow - you need to be sure you have defined your recursion well enough.
I have seen them perform marvelously as well. For instance we were able to take a tree recursion down from 30 minutes using a loop method to under 30 seconds for hierarchies of 700+ levels and 1.5 million people.
There is also potential problems for introducing circular references with a recursive cte that are easier to avoid in a while loop or cursor so you have to watch for that.

Hi Jason,

Heh... Tom and I are at the beginning of what promises to be a very interesting conversation (I love working with Tom... I learn something new everytime) about recursion and he's cited your post as an example. Before I can respond to Tom's fine and highly appropriate observation, I need to know just a bit more about your adventure in the quote above. If you have the time, I sure would appreciate it.

1. Did you traverse all 1.5 million "people nodes" in your code?

2. What was the purpose of the traversal? In other words, what did the code do with the hierarchy?

3. How was the loop code accomplishing the same thing? We know it was a loop but did it do something like Celko's "push stack" method or was it simply "read-a-row, write-a-row" or ???

4. I'm definitely not familiar with what you did and my meager questions are certainly not all encompassing, so any amplifying information above and beyond the questions above will be greatly appreciated.

Thanks for your time, Jason.

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

L' Eomot Inversé (11/5/2011)
That rCTEs are bad for counting doesn't make all use of rCTEs silly and it certainly doesn't demonstrate that rCTEs are not a set-based concept; would you claim that JOIN is not a set based concept just because it is possible to write silly triangular joins with appalling performance?

Recursive CTEs can be made to perform extremely well; our very own Chris Morris has posted a number of amazingly fast and creative recursive solutions on SSC over the years. The main reason recursive CTEs have a poor reputation for performance among a section of the SQL community is that very few people understand the 'recursive' query plan well enough to see how to make them really fly The best technical explanation of how the plan really works is by Craig Freedman:

http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx

You still need a couple of extra key concepts over and above the details given there, Chris M demonstrates one of them here:

http://www.sqlservercentral.com/Forums/Topic925065-8-1.aspx

And there's also a 'super-fast' DISTINCT (by me!) that Jeff may recall:

http://www.sqlservercentral.com/Forums/FindPost1013407.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jeff Moden (11/5/2011)
SQLRNNR (11/3/2011)
Yes recursive CTEs can be slow - you need to be sure you have defined your recursion well enough.
I have seen them perform marvelously as well. For instance we were able to take a tree recursion down from 30 minutes using a loop method to under 30 seconds for hierarchies of 700+ levels and 1.5 million people.
There is also potential problems for introducing circular references with a recursive cte that are easier to avoid in a while loop or cursor so you have to watch for that.
Hi Jason,
Heh... Tom and I are at the beginning of what promises to be a very interesting conversation (I love working with Tom... I learn something new everytime) about recursion and he's cited your post as an example. Before I can respond to Tom's fine and highly appropriate observation, I need to know just a bit more about your adventure in the quote above. If you have the time, I sure would appreciate it.
1. Did you traverse all 1.5 million "people nodes" in your code?
2. What was the purpose of the traversal? In other words, what did the code do with the hierarchy?
3. How was the loop code accomplishing the same thing? We know it was a loop but did it do something like Celko's "push stack" method or was it simply "read-a-row, write-a-row" or ???
4. I'm definitely not familiar with what you did and my meager questions are certainly not all encompassing, so any amplifying information above and beyond the questions above will be greatly appreciated.
Thanks for your time, Jason.

1. Yes

2. To write out the hierarchical tree to a table so other queries could use that rather than rebuild the tree each time a report needed to be run. Also, we used the same code in building out compensation/bonus earnings (think MLM).

3. Read a row/write a row.

4. No problems. I continue to learn with the recursive CTEs as I go. I am merely average when it comes to programming them and learn something new with each new recursion it seems. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

SQL Kiwi (11/6/2011)
L' Eomot Inversé (11/5/2011)
That rCTEs are bad for counting doesn't make all use of rCTEs silly and it certainly doesn't demonstrate that rCTEs are not a set-based concept; would you claim that JOIN is not a set based concept just because it is possible to write silly triangular joins with appalling performance?
Recursive CTEs can be made to perform extremely well; our very own Chris Morris has posted a number of amazingly fast and creative recursive solutions on SSC over the years. The main reason recursive CTEs have a poor reputation for performance among a section of the SQL community is that very few people understand the 'recursive' query plan well enough to see how to make them really fly The best technical explanation of how the plan really works is by Craig Freedman:
http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx
You still need a couple of extra key concepts over and above the details given there, Chris M demonstrates one of them here:
http://www.sqlservercentral.com/Forums/Topic925065-8-1.aspx
And there's also a 'super-fast' DISTINCT (by me!) that Jeff may recall:
http://www.sqlservercentral.com/Forums/FindPost1013407.aspx

Thanks for the references - I will have to check into these.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events