How can I make DbSet.Add() faster?

c# entity-framework performance sql-server-2008

Question

I have to import about 30k rows from a CSV file to my SQL database, this sadly takes 20 minutes.

Troubleshooting with a profiler shows me that DbSet.Add is taking the most time, but why?

I have these Entity Framework Code-First classes:

public class Article
{
    // About 20 properties, each property doesn't store excessive amounts of data
}

public class Database : DbContext
{
    public DbSet<Article> Articles { get; set; }
}

For each item in my for loop I do:

db.Articles.Add(article);

Outside the for loop I do:

db.SaveChanges();

It's connected with my local SQLExpress server, but I guess there isn't anything written till SaveChanges is being called so I guess the server won't be the problem....

1
30
12/4/2010 8:14:10 PM

Accepted Answer

Each item in a unit-of-work has overhead, as it must check (and update) the identity manager, add to various collections, etc.

The first thing I would try is batching into, say, groups of 500 (change that number to suit), starting with a fresh (new) object-context each time - as otherwise you can reasonably expect telescoping performance. Breaking it into batches also prevents a megalithic transaction bringing everything to a stop.

Beyond that; SqlBulkCopy. It is designed for large imports with minimal overhead. It isn't EF though.

9
12/4/2010 8:13:01 PM

Popular Answer

As per Kevin Ramen's comment (Mar 29) I can confirm that setting db.Configuration.AutoDetectChangesEnabled = false makes a huge difference in speed

Running Add() on 2324 items by default ran 3min 15sec on my machine, disabling the auto-detection resulted in the operation completing in 0.5sec.

http://blog.larud.net/archive/2011/07/12/bulk-load-items-to-a-ef-4-1-code-first-aspx



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow