Removing entities in EF6 is very slow using RemoveRange

Question

From reading many other posts it looks like I need to use

context.DbSet<Table>.RemoveRange(…);
context.SaveChanges();

to efficiently remove multiple entities.

Sadly however, in my scenario, this is still taking far too long. In tests, even removing 5 entities with about 10 fields takes about 1 sec per entity. This is far too slow.

What else can I do to improve performance?

Edit

This is what the method looks like that does the work:

  public void RemoveClassReportGroupings(IEnumerable<(int clientClassId, int classReportGroupingId)> enumerable)
        {
            List<Class_ReportGrouping> removeItems = new List<Class_ReportGrouping>();

            var dict = _context.ClassReportGroupings.Select(i => i).ToDictionary(i=> (i.ClassId, i.GroupingId));

            foreach (var item in enumerable)
            {
                var removeItem = dict[(item.clientClassId ,item.classReportGroupingId)];
                removeItems.Add(removeItem);
            }

            _context.ClassReportGroupings.RemoveRange(removeItems);

        }

How does your code inside `RemoveRange`look like? A short guess is, that you have a complicated select there or your database should have additional indexes. — Marcel, Oct 30 '19 at 06:53
Where does it take long now? At gathering your list, at calling RemoveRange or at SaveChanges? — Marcel, Oct 30 '19 at 07:13
@Marcel, The time proportions are: 1) Creating Dictionary - 6% , 2) RemoveRange - 2% , 3) SaveChanges - 92% — Cleve, Oct 30 '19 at 07:33

score 0 · Answer 1 · answered Oct 30 '19 at 07:56

I guess, that this has nothing to do with EF, but with database optimization.

SaveChanges is the place where Entity Framework actually compiles your LINQ query into a suitable SQL query and executes it to the Database.

You may want to retrieve the query EF created for deletion for manual examination and or have a look at the possible query optimizations using a tool like MS SQL SQL Server Management Studio (SSMS)

There are plenty of resources about query analysis and optimizations for SQL server, have a look around these, e.g from Microsoft (retired, but still relevant)

Thanks for the advice. I'll look into it. – Cleve Oct 30 '19 at 08:20 — Cleve, Oct 30 '19 at 08:20

score 0 · Answer 2 · answered Sep 23 '21 at 18:32

I ran into a issue in slowness using RemoveRange() and discovered it had to do with how it translates those calls into sql statements. If I asked it to deleted 10 records. It would make 10 separate delete calls if you monitored thru SQL profiler or used some debugging code in your debugger to see the queries.

Ended up using dbcontext.executesqlcommand() to write a query that would delete those records all at the same time. Much quicker for our purposes. It can become a very big deal if you have a large amount of records.

Note - a byproduct of this was the local dbcontext will not be aware of those changes, so you may need to deal with those separately.

Also note - 1 second to delete 1 record seems like you may have some underlying db issues such as a large amount of referential integrities or some other constraint that forces the DB to do more then delete. As a rule I expect EF to perform very similar to how a raw sql command performs when executed directly on the server.

Old question but came up while I was trying to solve a similar problem. Maybe helps someone in the future.

Removing entities in EF6 is very slow using RemoveRange

Edit

2 Answers2