Wednesday, 22 February 2012

sub-tree performance - summary

Note to self in regards to sub-tree performance in SQL Server.

When working with hierarchical structures in SQL Server never use XML data. It is usually tricky to implement and performs poorly. CTE recursive queries perform reasonably well and are very elegant in implementation and as such are suitable for wide range of applications but when the tree is big, wide and deep and performance is critical HierarchyId should be used. On smaller data sets it not only cumbersome but also slower than other solutions and the cross-over point seems to be around 1500 nodes.

Old loop solution performs consistently regardless of data size but somehow lacks elegance.

Detailed reasons for the above can be found in the following posts:

Part 1 - the brief introduction to the problem
Part 2 - test environment setup with all four methods explained
Part 3 - testing, results and the conclusion

1 comment: