Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Recursive Queries in SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, March 13, 2008 12:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Nice and wonderful article with some good examples. :)


Post #487093
Posted Friday, April 18, 2008 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 If
End 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, ETC
Property 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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, ETC
Property 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 order


When 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, Name
from HierarchyCTE
order 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, ETC
Property 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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, Name
from FTreeCTE
order 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, ETC
Property 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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, ETC
Property 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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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, ETC
Property 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 CTE

where “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
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse