Materialized path pattern VS Hierarchyid

Question

I am reading the SQL server 2008 bible and it says the materialized path pattern is significantly faster then the hierarchyid. Is this really true? How can I make the hierarchyid have equal or better performance.

Kaslik

Answer 1

The chapter explains three methods for designing and querying hierarchies: Adjacency Pairs, Materialized Path, and HierarchyID. These are three solutions to the same problem so yes, it makes perfect sense to compare these three methods. The truth is that Materialized path is the fastest but Adjacency Pairs can solve more types of hierarchy problems. HierarchyID is clumsy, difficult to query, and, if you follow MSFT’s recommendation, it only stores the relative position, not the key, so it’s less robust.

Amhara

Heh... I used to think that about the HierarchyID datatype and its methods. After using it for just a day, I don't find any of what you said about it being clumsy, of difficult to query. If maintained in the same table with the Adjacency list, I've found it to be quite robust and not the performance burden that most have claimed. Guess I'll have to dig into the "SQL server 2008 Bible" and see what's up. I'm also setting up some million rows tests between the HierarchyID and Nested Sets. – Ravi

Adjacency Pair or Self Join with the CTE Expression solved my issue where I had to get a list of Industry and Practise Codes. Thanks Paul! – Gynandrous

Answer 2

Do you mean materialized path vs. nested sets or ? A materialized path pattern can employ a hiearchyid datatype. It doesn't make sense to compare the speed of a pattern vs a datatype.

Dragrope

Yes, in the sql server 2008 bible - they compare the materialized path pattern and the hierarchyid. Even though the hierarchyid uses the materialized path it does not fully utilize all of its features. So the author compared them side by side and he said the materialized path is much more performant then the hierarchyid. I don't understand how this can be! – Kaslik