How can I make DbSet.Add() faster?

c# entity-framework performance sql-server-2008

Question

It takes 20 minutes to import roughly 30k entries from a CSV file to my SQL database.

Using a profiler for troubleshooting, I can see that DbSet. The most time is being spent on Add, but why?

I have the following classes for Entity Framework Code-First:

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; }
}

What I do in my for loop for each element is:

db.Articles.Add(article);

Beyond the for loop, I:

db.SaveChanges();

It's connected to my local SQLExpress server, but because nothing is written until SaveChanges is called, I don't think the server will be the issue.

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

Accepted Answer

Each component of a unit of work has overhead since it has to add to different collections, verify (and update) the identity manager, and so on.

The first thing I would attempt is batching into, say, groups of 500 (alter that amount as needed), and beginning with a new (new) object-context every time. If you don't, you can anticipate telescoping performance. A megalithic transaction that would have stopped everything is avoided by breaking it up into batches.

In addition, SqlBulkCopy. It is made with less overhead for big importers. But it's not EF.

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

Popular Answer

In line with Kevin Ramen's statement from March 29, I can attest to that setting.db.Configuration.AutoDetectChangesEnabled = false have a significant impact on speed

Running Add() On my computer, a procedure that by default took 3 minutes 15 seconds to complete on 2324 items finished in 0.5 seconds after the auto-detection was turned off.

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