Why is inserting entities in EF 4.1 so slow compared to ObjectContext?

Question

Basically, I insert 35000 objects within one transaction:

using(var uow = new MyContext()){
  for(int i = 1; i < 35000; i++) {
     var o = new MyObject()...;
     uow.MySet.Add(o);
  }
  uow.SaveChanges();
}

This takes forever! If I use the underlying ObjectContext (by using IObjectAdapter), it's still slow but takes around 20s. It looks like DbSet<> is doing some linear searches, which takes square amount of time...

Anyone else seeing this problem?

I somehow believe that the answer will be similar to this: http://stackoverflow.com/questions/5917478/what-causes-attach-to-be-slow-in-ef4/5921259#5921259 — Ladislav Mrnka, May 09 '11 at 22:55

score 132 · Accepted Answer · edited Dec 11 '19 at 11:16

132

As already indicated by Ladislav in the comment, you need to disable automatic change detection to improve performance:

context.Configuration.AutoDetectChangesEnabled = false;

This change detection is enabled by default in the DbContext API.

The reason why DbContext behaves so different from the ObjectContext API is that many more functions of the DbContext API will call DetectChanges internally than functions of the ObjectContext API when automatic change detection is enabled.

Here you can find a list of those functions which call DetectChanges by default. They are:

The Add, Attach, Find, Local, or Remove members on DbSet
The GetValidationErrors, Entry, or SaveChanges members on DbContext
The Entries method on DbChangeTracker

Especially Add calls DetectChanges which is responsible for the poor performance you experienced.

I contrast to this the ObjectContext API calls DetectChanges only automatically in SaveChanges but not in AddObject and the other corresponding methods mentioned above. That's the reason why the default performance of ObjectContext is faster.

Why did they introduce this default automatic change detection in DbContext in so many functions? I am not sure, but it seems that disabling it and calling DetectChanges manually at the proper points is considered as advanced and can easily introduce subtle bugs into your application so use [it] with care.

edited Dec 11 '19 at 11:16

Tohid

6,175
7
51
80

answered May 09 '11 at 23:48

Slauma

175,098
59
401
420

@Ladislav: Your're right I didn't find this as I was only searching for insert problems :-( – Hartmut May 10 '11 at 07:26
Thanks for the explanation. I was actually calling context.Configuration.AutoDetectChangesEnabled = false but I did it during database construction in the Seed() method. I thought this would set the default. I wasn't aware that I have to call it for each instance. Thanks! – Hartmut May 10 '11 at 07:29
3

@Hartmut: You could disable the change detection inside of the constructor of your derived DbContext, then you have it always disabled. But personally somehow this remark about "potentially introducing subtle bugs" when it is disabled makes me nervous. I have change detection on by default and disable it only in code blocks like yours where the performance boost is obvious and where I feel safe that it doesn't cause problems. – Slauma May 10 '11 at 09:16
I agree, I was only testing some performance critical part of my application. In production code it's best to limit it to cases like bulk inserts etc. – Hartmut May 12 '11 at 05:51
Thank you for this answer. Lots of information out there, but this cuts to the chase! – Fred Wilson Dec 16 '11 at 18:11
@Slauma That link is dead, afaict. Even with the parens added in http://msdn.microsoft.com/en-us/library/gg696177(v=vs.103).aspx – TankorSmash Dec 09 '12 at 15:42
@TankorSmash: Thanks! I've replaced the first link by a link to another reference and removed the second link (can't find a replacement for this quote). – Slauma Dec 09 '12 at 16:26
[Here](http://msdn.microsoft.com/en-us/data/jj556205.aspx) is a new copy of the link. – Casey May 13 '14 at 19:01
@emodendroket: Great, thank you! I've added the link to the answer. – Slauma May 13 '14 at 19:23

score 12 · Answer 2 · answered Oct 11 '17 at 11:36

12

In .netcore 2.0 this was moved to:

context.ChangeTracker.AutoDetectChangesEnabled = false;

answered Oct 11 '17 at 11:36

Maxvt

311
4
11

score 12 · Answer 3 · answered Mar 18 '12 at 17:30

12

Little empiric test with EF 4.3 CodeFirst:

Removed 1000 objects with AutoDetectChanges = true : 23 sec

Removed 1000 objects with AutoDetectChanges = false: 11 sec

Inserted 1000 objects with AutoDetectChanges = true : 21 sec

Inserted 1000 objects with AutoDetectChanges = false : 13 sec

answered Mar 18 '12 at 17:30

Zax

306
2
8

1

Thanks Zax. What are your results with 35,000 as per the question? You'll see that in the original question it tsates that the performance drops quadratically – Daniel Dyson Mar 19 '12 at 09:45

score 1 · Answer 4 · answered Apr 06 '13 at 17:25

Besides the answers you have found here. It is important to know that at the database level is is more work to insert than it is to add. The database has to extend/allocate new space. Then it has to update at least the primary key index. Although indexes may also be updated when updating, it is a lot less common. If there are any foreign keys it has to read those indexes as well to make sure referential integrity is maintained. Triggers can also play a role although those can affect updates the same way.

All that database work makes sense in daily insert activity originated by user entries. But if you are just uploading an existing database, or have a process that generates a lot of inserts. You may want to look at ways of speeding that up, by postponing it to the end. Normally disabling indexes while inserting is a common way. There is very complex optimizations that can be done depending on the case, they can be a bit overwhelming.

Just know that in general insert will take longer than updates.

Why is inserting entities in EF 4.1 so slow compared to ObjectContext?

4 Answers4

Linked