UOW.Remove terribly slow when number of objects grow

Hello,

1. I noticed a serious performance issue of UOW.Remove. When number of objects grow, Remove method gets very slow. Please see results of the attached tests (LightspeedTests1.Removeperformancemeasurement()). On my machine they are as follows:

   1. 100 objects:
      • Iter: 1. Total delete time 63 ms
      • Iter: 1. uow.Remove time 14 ms
      • Iter: 1. uow.SaveChanges time 31 ms
   2. 500 objects
      • Iter: 1. Total delete time 164 ms
      • Iter: 1. uow.Remove time 69 ms
      • Iter: 1. uow.SaveChanges time 69 ms
   3. 10000 objects
      • Iter: 1. Total delete time 7279 ms
      • Iter: 1. uow.Remove time 6204 ms
      • Iter: 1. uow.SaveChanges time 910 ms
   4. 50000 objects
      • Iter: 1. Total delete time 288468 ms
      • Iter: 1. uow.Remove time 280560 ms
      • Iter: 1. uow.SaveChanges time 6723 ms

   As you can see the SaveChanges times are linear or even better, but Remove times grow much faster (complexity looks exponential or so). Please check what is the reason of such performance hit.

2. When tracing SQL statements produced by LightSpeed, I noticed that SELECT queries are nicely optimized - i.e. for queries like this: "uow.MyDBObjects.Where(o => guids.Contains(o.Id))", and "guids" is array of Guid, LightSpeed produces a single query containing WHERE ... IN ().

   However DELETE queries executed while UOW.SaveChanges are always single - the same is for UPDATE queries. Couldn't they also be optimized? I suppose it would especially easy for DELETE.

Best regards, Dariusz Wąsacz, InstalSoft

re 1: The reason this is slow is due to the association on EntA which triggers the need for any associated entities to be updated. This in turn triggers a query for those children. This is not the intended way of bulk deleting objects, instead you should use a "remove by query" approach which avoids all of these overheads. See http://www.mindscapehq.com/documentation/lightspeed/Performance-and-Tuning/Bulk-Updates-and-Deletes for more details on this.

re 2: When deleting entities explicitly (e.g. UnitOfWork.Remove(entity)) they are always processed individually. As with #1 use UnitOfWork.Remove(query) if you want to bulk delete instances.

Jeremy

Hi Jeremy,

Thank you for the answer. I tried to use bulk delete, but it is only slightly better.

Re 1: The times are now:

1. 100 objects
   • Iter: 1. Total delete time 54 ms
   • Iter: 1. uow.Remove time 17 ms
   • Iter: 1. uow.SaveChanges time 35 ms
2. 500 objects
   • Iter: 1. Total delete time 179 ms
   • Iter: 1. uow.Remove time 87 ms
   • Iter: 1. uow.SaveChanges time 73 ms
3. 10000 objects
   • Iter: 1. Total delete time 4740 ms
   • Iter: 1. uow.Remove time 3749 ms
   • Iter: 1. uow.SaveChanges time 950 ms
4. 50000 objects
   • Iter: 1. Total delete time 101488 ms
   • Iter: 1. uow.Remove time 94467 ms
   • Iter: 1. uow.SaveChanges time 6890 ms

Additional info:

• for 100 objects it looks like the connection is not closed and db cannot be deleted after the tests.
• I have to split guids to chunks of 500 because otherwise I get "too many sql variables" exception.

Re 2: I also looked at the SQL executed on SaveChanges for bulk delete and it is no better than before. Each entity is deleted separately.

My tests are attached as usual.

Dariusz Wąsacz, InstalSoft

It looks like there's also another issue with this bulk delete. It doesn't delete objects created within the same UnitOfWork. A new test added to attached tests: BulkDeleteremovescreated_entities()

With regards, Darek

Hello Jeremy,

Is there any news in this subject (please see my previous 2 posts)?

Darek

You are actually using the UnitOfWork.Remove(IEnumerable) overload which is loading those entities so its equivalent to the previous incarnation. You want to use the UnitOfWork.Remove(query) overload instead, e.g.

foreach (var guidsChunked in aGuids.Split(500))
{
    var query = new Query(typeof(EntA));
    var guids = guidsChunked.Select(a => a.ToString()).ToArray();
    query.QueryExpression = Entity.Attribute("Id").In(guids);
    aUnitOfWork.Remove(query);
    aUnitOfWork.SaveChanges();
}

In terms of it not dealing with in-memory entities, thats correct - in the case of your IEnumerable call this is only going to find persisted entities because its sourcing the set from whats in the database. This is also true of UnitOfWork.Remove(query) as it bypasses the identity map. As suggested on the doc page linked above you need to ensure you have flushed your UnitOfWork prior to initiating this type of call and also call UnitOfWork.SaveChanges immediately after to flush the delete call.

Jeremy

Hello Jeremy,

Thank you very much for the answer. Now everything works fine :-) I thought that constructing a query the way I did it before, was equal to the method described in your post.

With best regards, Darek