I heard tales of performance problems associated with the HierarchyID datatype in SQL Server 2008. Since I haven't actually used the HierarchyID datatype (and, therefore, haven't used any of it methods up 'till now), I built myself a nice "little" million row "clean" table in the form of an Adjacency List (including an extra column called "Sales") and converted it to use the HierarchyID data-type. I used the indexes that Microsoft Suggested for such a thing.
Then, I built several queries just to try my hand at it (tried them on a smaller 14 node Hierarchy first, just to make sure things worked correctly). All of these queries are based on what I've been able to Google as "hierarchyid performance problems".
1. Find all Descendants (all employees in the "down-line" tree) for a given node.
2. Find all Ancestors (all managers in the "up-line" chain) for a given node.
3. Find all Siblings (all nodes at the same level) for a given node.
4. Find the SUM of sales for all Descendants 7 levels "down" for each of the million nodes in the entire hierarchy and insert into a new table.
Although I'm sure I could tweak a couple of things here and there insofar as indexing goes, I'm just not seeing what I would call either a performance problem or a resource usage problem.
So, my questions are... has anyone actually experienced a performance problem using the HierarchyID data-type and associated methods? If so, could you describe what you were doing and, perhaps, even post some code that demonstrates the problem?
Thanks for the help, folks.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs