CODEWITHSUNDEEP
sql-servergraphdb-structure
Laureano Bonilla
Laureano Bonilla

Reputation: 335

Appropriate implementation of Graph Tables structure for efficient querying trees under given node in SQL Server

I need to insert tree structured data into my database. Lets say for now as an example that the tree has n levels and I have a Node table called T_Node with a column called 'name' (the amount of columns might grow later) and an Edge table called T_Edge.

In T_Node I have several nodes and in T_Edge I have just one relationship per node where from_id would be a child node and to_id would be the parent node of that child.

In order to get the tree underneath a given 'NodeName' I would use a recursive query like this one:

WITH cte as (   
    
    Select 1 xlevel, fromName.name child, toName.name parent
    from  dbo.T_Node fromName, dbo.T_Node toName, dbo.T_Edge r 
    WHERE MATCH(fromName-(r)->toName) 
    and fromName.name = 'NodeName'
    UNION ALL
    Select xlevel + 1, fromName.name, toName.name
    from cte c, dbo.T_Node fromName, dbo.T_Node toName, dbo.T_Edge r 
    WHERE MATCH(fromName-(r)->toName) and c.parent = fromName.name
    )

    SELECT *
    FROM cte
    ORDER BY xlevel, parent, child

My question is: is this the best approach for using and querying the graph tables efficiently to get the tree underneath? Should I improve it by creating more relationships in T_Edge or creating another table?

Upvotes: 0

Views: 48

Answers (0)

Related Questions