How to increase insert speed using Bulk insert using AddRange and then SaveChanges in Entity Framework

c# c#-4.0 entity-framework entity-framework-6

Question

I have used Entity Framework to insert data into SQL tables.

For larger number of records, instead of Add(), I have used AddRange() and called SaveChanges() later.

It's still taking too much time to insert records - are there any solutions to increase the speed?

_Repository.InsertMultiple(deviceDataList);

await _Repository.SaveAsync();

public void InsertMultiple(List<string> deviceDataList)
{
    context.Devices.AddRange(devices);
}
2
-1
1/30/2019 5:30:48 PM

Accepted Answer

It doesn't matter if you use Add in a foreach or AddRange, problem lies in SaveChanges method, as it stores changes in observed entities one by one I think. There are libraries out there that allows for real bulk insert of entities using under the hood mechanism of SqlBulkCopy

Link to EF Core library: EFCore.BulkExtensions

EDIT: For EF6 I found this nuget: EntityFramework6.BulkInsert but I haven't personally used it so I can't say anything about it.

EDIT 2: I simplified this, using AddRange over Add will improve time of adding entities to change tracker, but still SaveChanges will could take very long time, so it's not a solution.

0
2/3/2019 3:27:46 PM

Expert Answer

Using AddRange over Add is already a great improvement. It fixes the part that's slow in the Application.

However, the SaveChanges still take a lot of time because one database round-trip is made for every entity you save. So if you have 10k entities to insert, 10,000 database round-trip will be made which is INSANELY slow.


Disclaimer: I'm the owner of Entity Framework Extensions

This library is not free but allows you to perform all bulk operations including BulkSaveChanges and BulkInsert:

  • Bulk SaveChanges
  • Bulk Insert
  • Bulk Delete
  • Bulk Update
  • Bulk Merge

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Bulk Operations
context.BulkInsert(customers, options => {
   options => options.IncludeGraph = true;
});
context.BulkMerge(customers, options => {
   options.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});
1
1/30/2019 2:19:11 PM


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