ansaurus

Question

Why is EF4 Code First so slow when storing objects?

Answer 1

+3 A:

Did you call SaveChanges() inside the loop? No wonder it's slow! Try doing this:

foreach(var recipe in The500000Recipes)
{
    cookRecipes.Recipes.Add(recipe);
}
cookRecipes.SaveChanges();

EF expects you to make all the changes you want, and then call SaveChanges once. That way, it can optimize database communication and sql to perform the changes between opening state and saving state, ignoring all changes that you have undone. (For example, adding 50 000 records, then removing half of them, then hitting SaveChanges will only add 25 000 records to the database. Ever.)

Tomas Lycken 2010-07-27 11:47:10

The loop was before any data is stored in db4o or EF4/SQL. So I first new up Recipe, then in a loop I add the RecipePreparations.So now I have a Recipe, with 50.000 RecipePreparation attached to it.I then store this in db4o or EF4/SQL. So a single db.store(recipe1) in db4o, and a single cookRecipes.Recipes.Add(recipe1);cookRecipes.SaveChanges() in EF4.

Saab 2010-07-27 11:53:32

DB4O is saving to a file on your local machine right? And EF4 has to open a database connection locally. The connection would typically remain open so it's a once-per-launch cost. Try adding a line to get the first item in the database before your timing loop for inserts to get that connection time out of the equation.

DamienG 2010-07-27 15:11:02

Answer 2

+1 A:

The EF excels at many things, but bulk loading is not one of them. If you want high-performance bulk loading, doing it directly through the DB server will be faster than any ORM. If your app's sole performance constraint is bulk loading, then you probably shouldn't use the EF.

Craig Stuntz 2010-07-27 13:23:40

Then I'm having doubts about the use of EF. When you're app relies on a very complex Model (domain objects) then I'd prefer an oo database (like db4o). But when the data is mainly tabular you would use a traditional relational database with, and optionally an OR/M like EF. But as you say EF fails when you do heavy bulk load/inserting/updating.So what I was afraid of is actually true. EF4 is only an option when doing very light database operations, and you're stuck to using a relational database.

Saab 2010-07-27 13:34:54

Again, not just EF, but *any* ORM will be slower than a DB server's bulk loading features. The EF may be slower than ORMs which support bulk inserts, but even those won't be as fast as the streaming APIs used on DB server dedicated bulk loading features. Bulk loading is a corner case for most apps, but if it's your bread and butter then you'd do better to use something like SSIS than an ORM.

Craig Stuntz 2010-07-27 13:40:56

I agree that most of the OR/M's will be slower that just bulk loading. But I have to disagree that an OR/M couldn't also make use of the bulk loading features of a database. The code is very easy to generate.But I think something is going wrong with EF4 Code First. Since just adding the Recipe entity to the dbContext takes a lot of time (130 seconds). Storing in the database (dbContext.Recipes.SaveChanges) is not a speed demon either, 70 seconds for 50.001 rows. Which translates into 50.000/70 = 714 row/s.

Saab 2010-07-27 13:53:22

I didn't say that they *couldn't* use bulk loading features. I said they generally *don't.* That said, if it's taking 130 seconds to add 1 entity then you have something entirely different going on. That is not normal performance. Maybe you're creating the DB? Code-first can do that implicitly.

Craig Stuntz 2010-07-27 14:00:56

Well that's what I used to do. But now it should connect to an existing database. But I have to check whether it's not just checking whether all the entities have the same structure as the tables in the database.

Saab 2010-07-27 14:06:34

Answer 3

A:

Just to add on to the other answers: db4o typically runs in-process, while EF abstracts an out-of-process (SQL) database. However, db4o is essentially single-threaded. So while it might be faster for this one example with one request, SQL will handle concurrency (multiple queries, multiple users) much better than a default db4o database setup.

mgroves 2010-08-24 15:38:31

ansaurus

tags:

views:

answers:

Why is EF4 Code First so slow when storing objects?

related questions