@paul-2 White,
Hey Paul... just so you don't think I'm blowing smoke (I know you don't but it makes me feel better to prove I'm not) or have lost my mind (heh... no help there :-P) by publishing "hear say" about performance problems with the HierarchyID, here's the one post that really got my attention and the reason why I dared say such a thing without first proving it myself...
Of particular import is where MS came back with the following...
We were able to track down the issue. [font="Arial Black"]The problem is that CLR calls, including hierarchyID's methods, are opaque to the query optimizer. This is by design. However, it means that the cardinality estimate for them can sometimes be quite wrong.[/font]
It happens that your EXISTS queries (the >0 query is transformed into an EXISTS) cause the query optimizer to choose a plan based on such an incorrect cardinality estimate. This causes the poor performance.
For now, the easiest workaround is to use a hint to force the use of the correct index. However, we will look into improving the cardinality estimation for hierarchyID methods to help address this sort of issue in the future.
Of course, the emphasis in the text is all mine. I imagine it's something to watch out for with all SQLCLR. It shouldn't scare anyone off of SQLCLR... they just need to be aware of what can happen.
And, as a sidebar, look what the MS respondent called it... "CLR". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.