Entity Framework bulk inserts - creating a large number of objects / entities is the slow part?

Question

There's a lot of information and answers on here and elsewhere about how to speed up bulk inserts with EF.

However, I'm only inserting about 10,000 records and the slow part of creating the entities.

First, I save the external data to a datatable, then loop through and for each row create a new entity, add the child entities in the loop (sourced from a couple of other datatables) and once the loop is finished call db.SaveChanges().

In my initial profiling, the db.SaveChanges() is slow, but nothing compared to the loop of creating all the objects in the first place - either as a separate List<entity> entities or directly to the context.

Is this usual? As of all the issues with bulk insert I can see, most seem to be related to the final commit to the database.

Edit to add some code Please excuse the psudo-code

DataTable ref1 = ConvertCSVtoDataTable(csv, firstRowsToDelete: 15); // Return's a Datatable from a CSV

foreach(string file in ListOfFilesToProcess)
{
    DataTable tbl = loadExcelFiles(file);

    foreach(DataRow dr in tbl.Rows)
    {
         Entity newEntity = new Entity();
         Entity.property1 = dr["Property1"].ToString();
         ... // Keep mapping properties to elements in the datatable
         Entity.Child.Add(new ChildEntity() { prop1 = ref1["ChildProp1"].ToString() });
         // Add the newly created entity to the context
         db.Entity.Add(newEntity);
    }
}
// Save the context
db.SaveChanges();

So by the point of saving the context there's a few thousand newEntity objects and their child navigation objects.

Iterating over the datatable and creating all these objects is the part which is the slowest!

Also, db.Configuration.AutoDetectChangesEnabled = false; has already been set.

If your processing CSV files, your better off not using entity framework. Use the native loader for whatever server your using, or if your database has a bulk load API, your better off using that. — Mike
– Mike, Commented Apr 18, 2016 at 14:58
@Mike there's one CSV and a number of Excel files. I could just parse them all into a SQL transaction but just learning about EF and its strengths and weaknesses. As per the question, there's loads of info about the slow performance of a bulk insert but nothing about the creation of 1000s of objects. Is it just me, or is it the same problem for everyone but they just think it's the bulk insert? Given some method reduce the insert to seconds, it must be just me right!? — RemarkLima
– RemarkLima, Commented Apr 18, 2016 at 15:49
Using AddRange() I am inserting roughly 10,000 records in about 1.3 seconds. Something is going on here if it still takes 20 secs to complete. — KyloRen
– KyloRen, Commented Mar 3, 2017 at 8:02

Jonathan Magnan · Accepted Answer · 2018-02-22 13:02:25Z

The slow part in your “creating all the objects” section is the Detect Changes.

You should always use AddRange over Add

Add: Detect changes for every object
AddRange: Detect changes only once (after all object are added)

This code should fix the slow part when creating objects:

DataTable ref1 = ConvertCSVtoDataTable(csv, firstRowsToDelete: 15); // Return's a Datatable from a CSV

List<Entity> list = new List<Entity>();

foreach(string file in ListOfFilesToProcess)
{
    DataTable tbl = loadExcelFiles(file);

    foreach(DataRow dr in tbl.Rows)
    {
         Entity newEntity = new Entity();
         Entity.property1 = dr["Property1"].ToString();
         ... // Keep mapping properties to elements in the datatable
         Entity.Child.Add(new ChildEntity() { prop1 = ref1["ChildProp1"].ToString() });

        list.Add(newEntity);
    }
}

// Add all newly created entities to the context
db.Entity.AddRange(list);

// Save the context
db.SaveChanges();

If after this fix, you still have some issue performance (from database this time), you should try a BulkSaveChanges / Bulk Insert third party library.

Here is an article about these libraries: Entity Framework - Bulk Insert Library Reviews & Comparisons.

Entity Framework Extensions (Recommanded, support everything)
EntityFramework.BulkInsert
EntityFramework.Utilities

Disclaimer: I'm the owner of the project Entity Framework Extensions

Thanks, AddRange() has made a huge difference, down from ~20s to milliseconds! However, it's still taking ~18s to create the list of entities, and this is only for 5200 entities (with ~8000 child items). So the question still remains, why does it take so long to create the list?
Just to make sure if I understand, it takes around 18s to loop on all rows and create the list? (Excluding the time taken for “AddRange” and “SaveChanges” code). If this is the case, you should post the full code inside the loop to make sure we point exactly where is the issue.
That's right, 18s on a i7, 16GB RAM machine just to create the list - before we get to the AddRange - There's a bit of reflection, and some further loops but debugging shows that each loop takes around 7ms to complete. I'll try to distill the loops into something readable otherwise they're just too long and mostly just mapping columns in the datatable to the entities properties.

Jonathan Allen · Accepted Answer · 2016-04-18 17:34:43Z

1

Just use a bulk insert. Even if you get past EF's ridiculously bad performance, you still have to contend with the database not liking individual inserts.

answered Apr 18, 2016 at 17:34

Jonathan Allen

70.6k77 gold badges276 silver badges459 bronze badges

Collectives™ on Stack Overflow

Entity Framework bulk inserts - creating a large number of objects / entities is the slow part?

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related