Performance issue on query over nested recursive objects

Question

I'm wondering what is the best way to select from a nested hierarchy of objects? Assume that we a class MyRecursiveObject as below:

 public class MyRecursiveObject
 {
   public Int64 Id { get; set; }
   public MyRecursiveObject Parent { get; set; }
 }

How can I reach to maximum performance while selecting all parent Ids of an instance of MyRecursiveObject?

Any suggestion is highly appreciated.

Are you using linq-to-sql or linq-to-entities or just anything that is fastest? — Joachim Isaksson, Mar 04 '13 at 15:59
If you're using linq on database derived data and SQL Server 2008+ it would be worth looking into hierarchyid: http://msdn.microsoft.com/en-us/library/bb677290.aspx. — Oliver, Mar 04 '13 at 16:00
I'm looking for both actually! Sometimes we are working on this issue in DAL and sometimes in logic layer. — M.Mohammadi, Mar 04 '13 at 16:01

Sergey Berezovskiy · Accepted Answer · 2013-03-04T16:10:27.620

You can use simple loop instead of recursion:

public IEnumerable<long> GetAllParentIdsOf(MyRecursiveObject obj)
{
    MyRecursiveObject child = obj;

   while (child.Parent != null)
   {
       child = child.Parent;
       yield return child.Id;
   }
}

Sample:

MyRecursiveObject obj = new MyRecursiveObject {    
    Id = 1,
    Parent = new MyRecursiveObject {
        Id = 2,
        Parent = new MyRecursiveObject { Id = 3 }
    }
};

GetAllParentIdsOf(obj).ToList().ForEach(Console.WriteLine);

// 2
// 3

score 1 · Answer 2 · answered Mar 04 '13 at 16:18

LinqToSql doesn't support walking trees of arbitrary depth. You should write a sql function with a TSQL While loop to generate the results, and call that sql function from linqtosql.

Something like:

DECLARE @MyNodeID int
SET @MyNodeID = 42
  -- we're looking for the path from this ID back up to the root
  -- we don't know the length of the path.

DECLARE @MyTable TABLE
(
  int ID PRIMARY KEY,
  int ParentID,
)

DECLARE @ID int
DECLARE @ParentID int

SELECT @ID = ID, @ParentId = ParentId
FROM MyRecursiveObject
WHERE ID = @MyNodeID

WHILE @ID is not null
BEGIN

  INSERT INTO @MyTable (ID, ParentID) SELECT @ID, @ParentID

  SET @ID = null

  SELECT @ID = ID, @ParentId = ParentID
  FROM MyRecursiveObject
  WHERE ID = @ParentID

END

SELECT ID, ParentID FROM @MyTable  --results

Performance issue on query over nested recursive objects

2 Answers2