How to optizmize multiple recursive Update in ASP.Net Core?

Question

Soo... I 'm now struggling with a difficult Task.

I have this recursive method:

private async Task DeleteUnitsCascade(int unitID) {
  var units = await _context.Units
    .Where(u => u.ParentID == unitID &&
                u.FromDate <= DateTime.Now &&
                (u.ToDate == null || u.ToDate >= DateTime.Now))
    .GroupBy(unit => unit.UnitID)
    .Select(grouping => grouping.OrderByDescending(unit => unit.Version).FirstOrDefault())
    .ToListAsync();
  if (!units.Any()) return;
  foreach (var unit in units) {
    var entityEntry = _context.Units.Attach(unit);
    entityEntry.Entity.ToDate = DateTime.Now;
    entityEntry.Entity.Position = 0;
    await DeleteUnitsCascade(unit.UnitID);
  }
}

And as you see I've tried to use async, but it have to wait for Task to end so it's like normal synchromous method. When I change:

await DeleteUnitsCascade(unit.UnitID);

To

DeleteUnitsCascade(unit.UnitID);

It's causing some strange results with MySQLConnector and DBContext. I've read a few similar question (Question), but it didn't help me understand. Also I've found this interesting article.

Can I somehow make all this recursive call run simultaneously?

Would resolving the recursion be an acceptable first step? You could do so by: create a queue, enqueue initial unitID, loop until queue is emtpy: dequeue head , do your stuff , for each subunits: enqueue unitIDs , next loop iteration. — Fildor, Oct 19 '18 at 09:00
Rather than storing your data with `(ParentID,unitID)`, consider using a nested set. It eliminates the need for recursion in this case and will provide a much more performant result. See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ , under "The Nested Set Model", for more information. That article focuses on MySQL, but the principle apples to any sort of structured data store. — 3Dave, Mar 20 '20 at 14:19

score 2 · Accepted Answer · answered Oct 19 '18 at 09:13

I think you can avoid those problems by avoiding the recursion:

private async Task DeleteUnitsCascade(int unitID) {
  var workQueue = new Queue<int>();
  workQueue.Enqueue(unitID);

  while( workQueue.Count > 0 )
  {
     int _unitID = workQueue.Dequeue();
     var units = await _context.Units
                               .Where(u => u.ParentID == _unitID &&
                                           u.FromDate <= DateTime.Now &&
                                           (u.ToDate == null || u.ToDate >= DateTime.Now))
                               .GroupBy(unit => unit.UnitID)
                               .Select(grouping => grouping.OrderByDescending(unit => unit.Version).FirstOrDefault())
                               .ToListAsync();
      if (!units.Any()) continue;
      foreach (var unit in units) {
        var entityEntry = _context.Units.Attach(unit);
        entityEntry.Entity.ToDate = DateTime.Now;
        entityEntry.Entity.Position = 0;
        workQueue.Enqueue(unit.UnitID);
      }
  }
}

It's working! But it's still executing 6s (same as recursion with `await`). Is there a way to run every `while` step async? — Morasiu, Oct 19 '18 at 09:24
I did not expect performance improvements. My focus was on making it work. — Fildor, Oct 19 '18 at 09:30
So... Is it a `yes` or `no` for possibility of running every step async? — Morasiu, Oct 19 '18 at 09:41
That would be a different question. But there is potential for parallel execution, yes — Fildor, Oct 19 '18 at 10:50

How to optizmize multiple recursive Update in ASP.Net Core?

1 Answers1