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();
}
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.
SqlBulkCopy. I'd also like to point out the existence of the Sql Server BULK INSERT command.Add them all in a loop and then save once at the end
foreach (var t in myCollection)
{
Db.Words.Add(t);
}
Db.SaveChanges();
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.
null, invalid relation) and all 250K of records fail. This is not the solution, see duplicate.
