Sequencing a recursive tree table

Question

Sequencing a recursive tree table

Anthony Niemann

Newbie

Points: 3
More actions
April 21, 2005 at 8:22 am

#88697

I have a table with these fields: RecordID, ParentRecordID, SiblingSequence, FullSequence.
RecordID is the primary key. ParentRecordID is the RecordID of the parent record in the tree. SiblingSequence is the sequence of this record within the records that share the same parent.
I need a routine that populates FullSequence from 1 to n as the index of all the records as if they were displayed in a fully expanded treeview (does that make sense).
I have written several procedures that work, but they all seem too slow. There are around 80K records in this table and the tree depth is unlimited but generally doesn't go past 10 levels.
My best performing procedure uses a while loop and calls itself recursively. (I started with a CURSOR, but knew I had to eliminate it before I could post here 😉
Anyone done something like this that might offer suggestions? I can post my proc if that would help.
Thanks,
Tony

Viewing 7 posts - 1 through 6 (of 6 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Anthony,

I think this is just an approach problem. If you need to populate those fileds once it does not really matter is it takes a little longer. My advice though is that some how you maintain thos fields at insert/Update time then all will be just reading.

My $0.02

hth

* Noel

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

I have done something similar and with a similar size table. I also used a recursive SELECT to populate the parent of each child until no more parents. The results go into a temporary table so that it only has to be done once. I haven't checked the exact run time since it is well under one second and insignificant in context.

I fully agree that a cursor would be inefficient and unnecessary. Avoid!

jagadish prasanna Grasshopper Points: 23 More actions · Answer 3

Here is what you cand do - have a column which will hold the hierarchy and

pre populate the hierarchy tree

say we have

M1

m2

m3

m4

m5

m6

Then the column for row containing m6 would display the hierarchy as

M1->m2->m4->m5

Hope this helps.

Jaggi

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Here's an excellent article from Joe Celko on nested sets:

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=123193

Not that you shouldn't go and buy his book, too.

Signature is NULL

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

A very interesting approach - thank you. I will buy the book!

Anthony Niemann Newbie Points: 3 More actions · Answer 6

Thanks for all the feedback. I read Joe's article and have ordered the book. Looks like it may take some time to digest however.

Tony