Why does DbSet.Add work so slow?

Question

The same topic was discussed here 8 months ago: How do I speed up DbSet.Add()?. There was no solution proposed other than using SqlBulkCopy which is not acceptable for us. I've decided to bring it up once again hoping there might be new thoughts and ideas around this issue and other workarounds are proposed. At least I'm just curious why this operation takes so long time to run.

Well, the problem is: I have to update 30K entities into database (EF 4.1, POCO). The entity type is quite simple containing integer Id + other 4 integer properties with no relations to other types. 2 cases:

• all them are new records. Running context.Entities.Add(entity) one by one for every entity takes 90 seconds with Cntx.Configuration.AutoDetectChangesEnabled=false (true value makes it run forever). Then SaveChanges takes just a second. Other approach: attaching it to the context like this takes the same 90 sec:

  Cntx.Entities.Attach(entity);
  Cntx.Entry(entity).State = EntityState.Added;
  

• all them are existing records with some changes. In the case it takes just few milliseconds to attach it to existing data context like this:

  Cntx.Entities.Attach(entity);
  Cntx.Entry(entity).State = EntityState.Modified;
  

  See the difference?

What is behind the scene of Add method that makes it work so incredibly slow?

Answer 1

I've got interesting performance testing results and I've found a culprit. I have not seen any information like this in any EF source I've ever read.

It turns out to be Equals overridden in a base class. The base class supposed to contain Id property shared between all types of concrete entities. This approach recommended by many EF books and pretty well know. You can find it here for example: How to best implement Equals for custom types?

More exactly, performance is killed by unboxing operation (object to concrete type conversion) that made it work so slow. As I commented this line of code it took 3 sec to run opposing to 90 sec before!

public override bool Equals ( object obj )
{
    // This line of code made the code so slow 
    var entityBase = obj as EntityBase;
    ...
}

As I found it I started thinking over what might be an alternative to this Equals. First idea was to implement IEquatable for EntityBase, but it happened not to be run at all. So what I decided finally to do is to implement IEquatable for each concrete entity class in my model. I have only few of them, so it's minor update for me. You can put whole Equal operation functionality (usually it is 2 object Ids comparison) into extension method to share between concrete entity classes and run it like this: Equal((EntityBase)ConcreteEntityClass). The most interesting, this IEquatable speeds up EntitySet.Add 6 times!

So I have no more issues with performance, the same code runs for me with less than a second. I got 180 times performance gain! Amazing!

Conclusion:

1. the most fast way to run EntitySet.Add is to have IEquatable for the specific entity (0.5 sec)
2. Missing IEquatable makes it run 3 sec.
3. Having Equals(object obj) which most sources recommend makes it run 90 sec