Fabio,

Thanks for the link.

Our tree is on average 6 levels deep. and it would never exceed 12 levels of depth.

After some performance testing we ended up with a hardwired selfjoin 12 levels deep in the stored procs. We have one sp that gets all the nodes in a subtree, and another that gets the path from a node back to the root.

They look a bit gnarly, but since it's essentially replicated code it's easy enough to maintain. The good thing is it runs very, very fast and performance is of utmost importance for this particular part of the database. It's much faster than any version of dynamic code that could handle an arbitrary tree depth.

Our tree maintenence GUI is set up to warn us should the tree depth ever get within 2 levels of the limit of the SP joins.

>Yes! This is an amazing topic! Joe Celko's technique has saved one big project we run here at the company. Basically, his technique (called adjacency list) stores the tree nodes hierarchies as a Graph and frees you from having to store the parent/child foreing-key references into the relational tables, which, BTW, is very annoying, given the need of several INNER JOIN constructs we have to build in order to retrieve the ancestors/descendents sets.