SQL Server 2017 and Azure SQL Database introduced https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview?view=sql-server-2017 used to model many-to-many relationships. Th...
Updated Jun 26, 2019
Version 3.0
Blog Post
Thank you Shreya Verma, this is a great article and inspires hopes for using graph db for BOM traversing in SQL Server 2019.
However in all my testing it performed poorly comparing to using recursive-CTE-based tree explosion.
The execution times were 10 to 20 times longer, output Rows estimates 4 to 10 times worse, plan cost comparison always 0% for recursion and 100% for the graph query using SHORTEST_PATH().
---------
Is this a known issue?
At this time it looks like Graph Db in SQL Server was created to support MATCH clause rather then improve performance of hierarchical data querying.
---------
Can you recommend any remedies?
- I played with different indexing on the node and edge tables. An index on the edge ($from_id, $to_id) helps, but not dramatically.
- OPTION (HASH JOIN) helps when querying for All product trees, but not for One product tree.
The sample data I used had 5 levels of hierarchy.