Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Recursive Queries in SQL Server 2005 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, March 13, 2008 12:03 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, July 16, 2012 10:19 AM Points: 46, Visits: 177
 I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?
Post #468942
 Posted Friday, April 18, 2008 5:49 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, April 20, 2016 3:23 AM Points: 6,049, Visits: 1,407
 Nice and wonderful article with some good examples. :)
Post #487093
 Posted Friday, April 18, 2008 6:37 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, September 22, 2010 6:11 AM Points: 2, Visits: 18
 Here's the SQL factorial:`With Fact (Num, Factorial)As( Select 0 as Num, convert(bigint, 1) as Factorial Union All Select Num + 1, convert(bigint, Num + 1) * Factorial from Fact Where Num + 1 <= 20 --Recursion Buster; not really necessary since Fact(21) doesn't fit in a BigInt anyway)Select * from Fact`
Post #487136
 Posted Friday, April 18, 2008 6:51 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 PW (3/9/2005)The sample VB.net code for comparison contains both syntax and logic errors.In VB.Net, an IF is ended by "End If", not End.Also, " Refactored code: Private Function Factorial(ByVal number As Integer) As Integer If number = 0 Then Return 1 Else Return (number * Factorial(number - 1)) End IfEnd Function Shouldn't the exit condition for this be "if number = 1"? Since you're multiplying by "number -1", and "1 -1" = 0, it seems like you would end up multiplying by 0, and thus getting 0 as your result every time. (It's been a while since I played with VB, so I'm not certain on this.) - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487149
 Posted Friday, April 18, 2008 6:57 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Drew Burlingame (3/17/2006)Thanks for taking the time to post this article.I'm curious as to performance compared to other methods of getting heirarchical data like adjacency, nested, etc.?The CTE method of unrolling a hierarchy uses the adjacency model. It's just another way to handle that, rather than cursors/while loops. I've tested cursors, while loops, and CTEs for complex adjacency hierarchies, and CTEs are faster in all of my tests (up to 7,000 nodes on the hierarchy, up to 50 levels). Cursors are the slowest. A semi-set while loop is in between.Nested sets are much faster to unroll. And they require one table scan per query, instead of a number of table scans equal to the number of nodes in the the hierarchy (cursor or CTE) or a number of scans equal to the number of levels (while loop). The problem with them is if the data changes much, they are much more difficult to handle. (Nested sets hierarchies select very, very fast, but update/insert/delete much more slowly; adjacency selects more slowly, but updates/inserts/deletes very efficiently.) - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487156
 Posted Friday, April 18, 2008 7:01 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Satish Jha (5/24/2006)Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical orderWhen you select from the CTE, you can use any of the usual structures for select statements. You can use aggregates (sum, count, et al), you can use joins, you can use Where clauses, Group By, Having, and Order By.Just treat the CTE the same way you would any other derived table.For example:`;with HierarchyCTE (Lvl, ID, ParentID, Name) as (select 1, ID, ParentID, Name from dbo.Hierarchy union all select Lvl + 1, h2.ID, h2.ParentID, h2.Name from dbo.Hierarchy h2 inner join HierarchyCTE on h2.ParentID = HierarchyCTE.ID)select Lvl, ID, ParentID, Namefrom HierarchyCTEorder by Name` - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487157
 Posted Friday, April 18, 2008 7:05 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Rabia Mansour (3/18/2007)Thanks for the article.My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.I've added : It is not reasnable in this example but it is reasnable in other.How it should be done to accomplish this need.It's just a question of building the join in the part of the CTE after the Union All.For example:`;with FTreeCTE (Generation, ID, Parent1ID, Parent2ID, Name) as (select 1, ID, Parent1ID, Parent2ID, Name from dbo.FamilyTree union all select Generation + 1, ft2.ID, ft2.Parent1ID, ft2.Parent2ID, ft2.Name from dbo.FamilyTree ft2 inner join FTreeCTE on ft2.Parent1ID = FTreeCTE.ID or ft2.Parent2ID = FTreeCTE.ID)select Generation, ID, Parent1ID, Parent2ID, Namefrom FTreeCTEorder by Generation` - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487160
 Posted Friday, April 18, 2008 7:07 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 MikeAngelastro (3/13/2008)I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487161
 Posted Friday, April 18, 2008 7:08 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Good article, by the way. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #487162
 Posted Friday, April 18, 2008 10:39 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, July 16, 2012 10:19 AM Points: 46, Visits: 177
 GSquared (4/18/2008)MikeAngelastro (3/13/2008)I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.GSquared, Thanks for your input.I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none. Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:SELECT DISTINCT FROM CTEwhere “CTE” is the CTE’s record set result - extra rows and all.And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?Thanks,Mike
Post #487358

 Permissions