Entity Framework 6 DbSet AddRange vs IDbSet Add - How Can AddRange be so much faster?

c# entity-framework entity-framework-6

Question

On my own PC, I was experimenting with Entity Framework 6 when I decided to try entering 430k or so rows.

Although I am aware that my initial attempt may have been better, it was nevertheless for research purposes:

var watch = System.Diagnostics.Stopwatch.StartNew();
foreach (var event in group)
{
    db.Events.Add(event);
    db.SaveChanges();
}

var dbCount = db.Events.Count(x => x.ImportInformation.FileName == group.Key);

if (dbCount != group.Count())
{
    throw new Exception("Mismatch between rows added for file and current number of rows!");
}

watch.Stop();
Console.WriteLine($"Added {dbCount} events to database in {watch.Elapsed.ToString()}");

I started it in the evening and came home to check it out. The outcome was this:

enter image description here

As you can see, the first 64523 events were added in 4 hours and 41 minutes, while the subsequent 66985 events took 14 hours and 51 minutes to be added. The application was still entering events into the database, although very slowly, when I examined the database. The "new" AddRange technique for DbSet was my next choice.

I changed my models toIDbSet to DbSet and changed theforeach with this loop:

db.Events.AddRange(group);
db.SaveChanges();

enter image description here

Now, I could add 60k+ events in little under a minute. Although it may not be lightning-fast, it is still a significant improvement. What is being on behind the scenes to do this? I had intended to check.SQL Server Profiler However, it would be helpful to include a description of what occurs in the code as well.

1
9
4/26/2017 8:11:05 PM

Accepted Answer

Jakub responded, noting that invoking SaveChanges after each inserted object was ineffective. But even if you relocate it outside, you would still have some performance issues. That won't resolve the performance problem brought on by the Add method.

Add versus AddRange

Using the Add method to add several things is a pretty frequent mistake. The DetectChanges technique is really INSANELY sluggish.

  • After each record is added, the DetectChanges function calls Add.
  • After all entries have been added, the AddRange function calls DetectChanges.

look at Performance Addendum for Entity Framework


It is perhaps not SqlBulkCopy fast, but it is still a huge improvement

Performance that is VERY CLOSE to SqlBulkCopy may be achieved.

I am the project's owner, according to Disclaimer.

This library is NOT free.

By enabling you to store several entities simultaneously, this module helps improve the efficiency of your work. There is support for all bulk operations:

  • BulkSaveChanges
  • BulkInsert
  • BulkUpdate
  • BulkDelete
  • BulkMerge
  • BulkSynchronize

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 Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});
15
8/22/2018 10:25:09 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