January 10, 2006 at 3:37 am
I'm sure this is a common problem but Google hasn't helped and while I have a working solution performance is, well let's just say performance is an issue.
Using SQL 2000 SP4
I have a table of cost centres with fields hry_child and hry_parent. The hierarchy is ragged i.e. some branches have more levels than others. What I need to do is given a particular cost centre, produce a set of all its last descendants, e.g. given the region code for Scotland, find all the branches in Scotland disregarding all the levels of the hierarchy in between.
I currently have a stored procedure to do this which runs as follows:
Pseudocode
Create a cursor to list out all the children of my start cost centre
If there are no children of the start cost centre, write the location code to a table, otherwise
Loop through the cursor and see if these children have children of their own
If they do not, they are last descendants and write the location code to a table
If they do have children, call the same stored procedure recursively
Code
ALTER PROCEDURE up_surveybranchlist @parent char(6) AS
BEGIN
DECLARE @child char(6)
DECLARE cs1 CURSOR LOCAL FOR SELECT hry_child FROM dbo.tbl_Structure_CostCentre_hierarchy WHERE hry_parent=@parent
OPEN cs1
FETCH NEXT FROM cs1 INTO @child
IF @@FETCH_STATUS<>0
INSERT INTO dbo.tblSurveyBranchList (sbl_BranchNo) VALUES (@parent)
WHILE @@FETCH_STATUS=0
IF NOT EXISTS (SELECT hry_child FROM dbo.tbl_Structure_CostCentre_hierarchy WHERE hry_parent=@child)
INSERT INTO dbo.tblSurveyBranchList (sbl_BranchNo) VALUES (@child)
ELSE
EXEC up_surveybranchlist @child
FETCH NEXT FROM cs1 INTO @child
END
CLOSE cs1
DEALLOCATE cs1
While having the advantages of a) producing the desired result and b) me being able to understand it, it's just too slow when starting near the top of the tree. I couldn't think of a noncursor solution - any takers out there?
Thanks for any help
--
Scott
January 10, 2006 at 5:22 am
You might want to check out this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=242390
January 10, 2006 at 6:17 am
That's *so* much faster (and it was your suggestion in that thread I used). Thanks you very much
--
Scott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply