Finding last descendants in a hierarchy

Question

Post reply

Finding last descendants in a hierarchy

Scott-144766

SSCarpal Tunnel

Points: 4238
More actions
January 10, 2006 at 3:37 am

#92897

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