Writing to large number of records take too much time using Entity Framework

Question

I'm working on a project that just inserts relatively large number of records (250K records, just 2 columns) using Entity Framework code-first..

But it takes too much time to write all the records to the database (2~3 hrs)!

Is it because Entity framework is slow with large number or records or what ?

P.S.

Code snippet:

foreach (var t in myCollection)
{      
  Db.Words.Add(t);
  Db.SaveChanges();
}

Laoujin · Accepted Answer · 2017-05-15T14:34:59.990

5

Entity Framework is not a good fit for bulk operations. Even when doing the Context.SaveChanges() at the end (or every 100 records or something).

using (var context = new MyContext()) 
{
    // This might speed up things a little aswell
    context.Configuration.AutoDetectChangesEnabled = false;

    // As might this (if applicable for your situation)
    context.Configuration.ValidateOnSaveEnabled = false;
}

For best performance, you might want to use something close to the database like a stored procedure or if it has to be in code, use ADO.NET directly.

SqlBulkCopy might be exactly what you want.

edited May 15 '17 at 14:34

answered May 15 '17 at 14:29

Laoujin

9,962
7
42
69

+1 for mentioning `SqlBulkCopy`. I'd also like to point out the existence of the Sql Server [BULK INSERT](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql) command. – EJoshuaS - Stand with Ukraine May 15 '17 at 14:55

score 2 · Answer 2 · answered May 15 '17 at 14:27

2

Add them all in a loop and then save once at the end

  foreach (var t in myCollection)
  {      
      Db.Words.Add(t);
  }

  Db.SaveChanges();

answered May 15 '17 at 14:27

Philip O'Reilly

29
3

To clarify, it's better to store your changes and then write once than send write requests over and over again. – CDove May 15 '17 at 14:28
4

Also the OP has 250k records to insert. Doing a single `SaveChanges` on that many records will cause other issues. Probably better to batch them up in groups of 100/500 or use a proper bulk insert library. – DavidG May 15 '17 at 14:30
1

Yeah and now one record has issues (too long of a string, non-nullable column being `null`, invalid relation) and all 250K of records fail. This is not _the_ solution, see duplicate. – CodeCaster May 15 '17 at 14:31
Yeah, It speeded it up a bit more (~1000 record/sec instead of ~100 record/sec) on my machine, but this is still risky and relatively slow.. – Gamal Mohamed May 15 '17 at 14:46
I am not sure what you mean by risky but Entity Framework will always perform poorly with bulk inserts. – Philip O'Reilly May 15 '17 at 14:49
I mean risky as per the case mentioned by @CodeCaster comment for example.. – Gamal Mohamed May 15 '17 at 14:50

Writing to large number of records take too much time using Entity Framework

2 Answers2