0

I currently working on a application that use Entity Framework 4.1 (code first) and SQL Server CE 4.0. One of the new things I really like from SQL Server CE 4.0 are the computed values.

But I have some performance problem while importing old data into my new data store system (around 50000 entries). This is my second implementation for this. The first version used Entity Framework 4.0 and SQL Server CE 3.5. But there is a huge performance difference between these implementations. The code first implementation takes around one hour to import the entries, the other implementation only some minutes.

The most time is spend in the SaveChanges method. I tracked the problem down to the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute. If I use [DatabaseGenerated(DatabaseGeneratedOption.None)] and implement my own generation of keys (as a simple workaround) the performance is back on the level of the first implementation.

Is this a known problem? Is there any way to solve this performance problem? Or is generating my own keys the way to go?

Fox32
  • 13,126
  • 9
  • 50
  • 71
  • Here are two questions/answers about bulk inserts in EF 4.1: http://stackoverflow.com/questions/5943394/why-is-inserting-entities-in-ef-4-1-so-slow-compared-to-objectcontext/5943699#5943699 and http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework/5942176#5942176 Key is mainly to disable automatic change detection. I don't see a relation to the `DatabaseGeneratedOption` though, but maybe it helps anyway. – Slauma Jul 11 '11 at 16:15
  • Disabling AutoDetectChangesEnabled doesn't really improve the performance in my case (But I do it). The speedup of not using DatabaseGeneratedOption.Identity is much higher. – Fox32 Jul 12 '11 at 07:23

2 Answers2

0

Keep an ObjectContext / connection open for the liftime of your app. EF is not ideal for bulk inserts, use SqlCeResultSet for that for max perf.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • I already keep the DBContext open for the whole import process, but committing in small units of 500 entries seems to be faster. The problem is that the first version was fast enough for my needs. – Fox32 Jul 11 '11 at 06:50
0

I decided to go the "generate my own key" way as a workaround. I added the required features for key generation to my DBContext class.

If Microsoft change something on this behavior, I switch back to auto generation.

Fox32
  • 13,126
  • 9
  • 50
  • 71