-1

Here is my data structure look like a tree of nodes that I keep all of them in one recursive table:

Id   | Name    | ParentId  | Level
-----------------------------------
1    | Node1   | NULL      |  1
2    | Node2   | NULL      |  1
3    | Node3   | 1         |  2
4    | Node4   | 1         |  2
5    | Node5   | 3         |  3
6    | Node6   | 3         |  3
7    | Node7   | 3         |  3
8    | Node8   | 4         |  3
9    | Node9   | 6         |  4
10   | Node10  | 6         |  4
11   | Node11  | 8         |  4
12   | Node12  | 10        |  5

The problem that I have is retrieve children and parents of one Node is not performed very well, I tried to call the recursive function to fetch the children and parents. for example for Node6

retrieveAllChildrenof(6){} -> return 9,10,12;

retrieveAllParentsof(6){} -> return 3,1;

So I just thinking to add a new column to avoid recursive function, something like this:

Id   | Name    | ParentId  | Level  | Trace
---------------------------------------------
1    | Node1   | NULL      |  1     |  1
2    | Node2   | NULL      |  1     |  2
3    | Node3   | 1         |  2     |  1-3
4    | Node4   | 1         |  2     |  1-4
5    | Node5   | 3         |  3     |  1-3-5
6    | Node6   | 3         |  3     |  1-3-6
7    | Node7   | 3         |  3     |  1-3-7
8    | Node8   | 4         |  3     |  1-4-8
9    | Node9   | 6         |  4     |  1-3-6-9
10   | Node10  | 6         |  4     |  1-3-6-10
11   | Node11  | 8         |  4     |  1-4-8-11
12   | Node12  | 10        |  5     |  1-3-6-10-12

Now with trace field I could retrieve the children and parents as:

retrieveAllChildrenof(6){} -> 
//Trace(6) = 1-3-6; GetAllNodesThatStartWithTrace(1-3-6) => 9,10,12;

retrieveAllParentsof(6){} -> //Trace(6) = 1-3-6; GetNodesIdsIn(1-3); => 1,3;

I know the comma-separated or dash-separated data is hard to process but is there any better way to navigate in nodes and find children and parents, any suggestion?

UPDATE Currently for recursive functions I used like this: CTE Recursion to get tree hierarchy

Saeid
  • 13,224
  • 32
  • 107
  • 173
  • 1
    Please share the code you use to retrieve the children and parents. What is in those functions? The schema you started with is a fairly common paradigm for tree representation in SQL. Ensure you have set up the PK, FK relationships properly and indexed both PK and FKs – user1443098 Aug 16 '18 at 13:48
  • @user1443098 The functions is simple recursive call by parentId, the problem is not indexing, I'm wondering to retrieve data as fastest as possible, regardless of how we set the indexes. – Saeid Aug 16 '18 at 13:51
  • 1
    but the recursive call could be a while loop or a recursive cte or cross apply which negates recursion... all these things can perform differently. the function would help. – S3S Aug 16 '18 at 13:59
  • here comes your answer; https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy – Serkan Ekşioğlu Aug 16 '18 at 14:00
  • When the problem is performance, the solution is usually indexing. – Tab Alleman Aug 16 '18 at 14:00
  • @scsimon I uodate the question. But as I said I am wondering to use not recursive function – Saeid Aug 16 '18 at 14:05
  • Please share your CREATE TABLE statement, showing proper PK/FK relationships and indexing. – user1443098 Aug 16 '18 at 14:09
  • https://en.wikipedia.org/wiki/Nested_set_model works well without using recursive queries. – btilly Aug 16 '18 at 22:34
  • Any reason not to use hierarchid functionality in sql server which is optimized for managing this? – TomTom Aug 17 '18 at 04:32
  • @TomTom I will Check, Thank you – Saeid Aug 17 '18 at 15:19

1 Answers1

0

If the tree changes only rarely you could do a depth first search, numbering the nodes as you come to them. The descendants of a node have consecutive numbers starting just after the number allocated to that node, and you could use a column to record each node's number and a column to record the number of its last descendant. Then retrieve all descendants of a node by looking up its number, the number of its last descendant, and retrieving all rows with numbers between these two.

mcdowella
  • 19,301
  • 2
  • 19
  • 25