Just in case those that already responded on this thread haven't seen them, the articles that I was talking about writing came out a while ago. Here are the links.
For some very high performance methods for building/maintaining (54 seconds for a million node hierarchy) from an Adjacency List and using Nested Sets in conjunction with both an Adjacency List and a Hierarchical Path all in one table, please see the following article.
[font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/font][/url]
For information on how to build a pre-aggregated hierarchical table (again... only takes about a minute) that answers for most of the things you'd ever query a hierarchical table for (MLM'ers will love this one), please see the following article.
[font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.