[转]Entity Framework and slow bulk INSERTs

本文转自：https://weblog.west-wind.com/posts/2013/Dec/22/Entity-Framework-and-slow-bulk-INSERTs

I’ve been working on an internal project where I needed to load up a large amount of records into a database for testing. On this project I’m using Entity Framework Code First 6.02 and since I’m in initial testing mode I decided to create a DbInitializer that adds about 30,000 records to the database. I figured this was a reasonable idea for the moment as I’m testing out a few different scenarios for handling a large amount of data.

I ran into a major problem with the code I initially used however, which is simply using Context.Table.Add() to add these records to two tables in my DbContext:

private void SeedInitialData(WebMonitorContext context) { for (int i = 0; i < 10000; i++) { var user = new User() { Email = DataUtils.GenerateUniqueId() + "@.test.com", FirstName = DataUtils.GenerateUniqueId(), LastName = DataUtils.GenerateUniqueId(15), Company = DataUtils.GenerateUniqueId(12), StreetAddress = DataUtils.GenerateUniqueId(50), City = DataUtils.GenerateUniqueId(), State = "CA" }; user.Password = AppUtils.EncodePassword("password", user.Id); context.Users.Add(user); var site = new WebMonitorSite { UserId = user.Id, SiteName = "West Wind Technologies", Url = "http://west-wind.com/", SearchFor = "West Wind Technologies" }; context.Sites.Add(site); site = new WebMonitorSite { UserId = user.Id, SiteName = "GeoCrumbs", Url = "http://geocrumbs.net/", SearchFor = "GeoCrumbs" }; context.Sites.Add(site);

context.SaveChanges(); }

// context.SaveChanges(); // also tried this even slower }

So then, in order to get the database created, I ran a test that simply returned a few records from the users table and waited… and waited and waited…

At first I thought something was wrong, but I checked the DB and saw the database created, and records getting added, very, very slowly. At some point later I let this run out completely and found that it took 57 minutes to complete adding the 30,000 records. Yikes…

Now granted in a production application I wouldn’t recommend to do this kind of INSERT batch in a DbInitializer, but the problem that occurred here is something that can crop up in other places as well as you’ll see in a second.

Replacing with ADO.NET Code

Just to make sure it wasn’t issue with my local Sql Server instance I re-ran the same operations using my own DbDataAccess::SaveEntity() helper (part of the West Wind Utilities library in the West Wind Toolkit) which is a generic based Sql mapper that maps properties to database field in a flat table using Reflection and ADO.NET – IOW, it’s not super fast but easy to use on simple one-dimensional objects like this.

So instead of the Context.Table.Add() and context.SaveChanges() I simply replaced the Add() calls with the SaveEntity method:

context.Db.SaveEntity(user, "Users", "id");

and

context.Db.SaveEntity(site, "WebMonitorSites", "id");

Running this takes a mere 25 *seconds* for those same 30,000.

Context Bloat and Large Lists

My first thought was, this can’t be right and as it turns out there are a few ways to make EF work a lot faster in this scenario.

The issue here is that I’m creating a lot of records and I keep adding them to the same context instance. This means that by the time I’m done this DbContext will end up tracking 30,000 records. So the first few inserts probably run pretty fast, but as the list of records grows it gets slower and slower as EF tries to track and ever larger object graph. I’m not exactly sure why this should happen in this case as the data being added are very simple records into a two table schema with no relations, but clearly EF does more than just property change tracking as items are added to the context.

There are two easy fixes to this problem:

Keep the active Context Graph small by using a new context for each Unit of Work
Turn off AutoDetechChangesEnabled

Use a Unit of Work and a new Context for each set of Records

This approach basically never lets the Context get very large by recreating the context inside of the loop. So this would look like this:

for (int i = 0; i < 10000; i++) { var context = new WebMonitorContext();

… add items to the context tables

context.SaveChanges();

}

Making this little change (in a test, not the Iinitializer!) the performance now comes down to something reasonable: The same 30,000 inserts take 33 seconds.

This is a good practice in general. Building up large trees is a terrible idea inside of an application as you are making it more likely to cause lock conflicts or otherwise letting the data get stale. In most situations unit of work saves are more efficient.

This is one way to solve the Context graph size problem, but unfortunately this doesn’t work for my DbInitializer, because I don’t control the instantiation of the context when the initializer is called – EF passes the context in so I can’t create a new one.

Turn off AutoDetectChangesEnabled

Another solution suggested by @julielerman is to turn off AutoDetectChangesEnabled. This confirms that the problem in and of itself is the change tracking in EF on large datasets. Turning off this flag on the context also brings the time down to about 20 seconds and – more importantly - it also works for my DbInitializer since I can simply apply the flag on the existing passed in context:

context.Configuration.AutoDetectChangesEnabled = false;

//EFCore _dbContext.ChangeTracker.AutoDetectChangesEnabled = false;

for (int i = 0; i < 10000; i++)
{

… do the Context.Add() / SaveChanges() here as before

}

This is the easiest solution in this case and it actually turns out to also be quite a bit faster.

AutoDetectChangesEnabled should be used very selectively if at all. There are other mechanisms such as AsNoTracking() that can be a better solution to avoid change tracking of lists. But in this case it solves a specific problem. Just make sure that when you’re done with your non-change tracking required operations that you turn the flag on again, or probably even better that you kill and recreate your context altogether (Unit of Work ideally but even in the current UOW scope if necessary).

Batching

Several people on twitter also suggested that instead of calling SaveChanges() on each unit of work, it’d be more efficient to batch a number of commands together which I also added on the bottom of the loop:

context.Configuration.AutoDetectChangesEnabled = false;

for (int i = 0; i < 10000; i++)
{
… do the Context.Add() / SaveChanges() here as before

if (i % 50 == 0) context.SaveChanges(); }

context.SaveChanges() // save whatever is left over

And this knocks off another 4 seconds – down to 16 seconds which is good enough for me.

Another few people suggested that for large data imports, SQL Bulk Insert is probably a better choice, but you have to use tools other than Entity Framework to handle implementing this functionality. Personally I think that’s overkill unless the ultimate performance is required AND there’s actually a convenient way to get the data into the format that SQL Bulk Insert can import from such as CSV files. Creating those files in the first place can be slow for example and might negate any perf benefits you might get from the bulk insert in the first place.

DbTable.AddRange()

Another option for adding records is by using DbTable.AddRange() to build up the list of records in memory first and then submit the entire list at once in a single command. In this case the change detection doesn’t start until after all the records have been added.

To do this I can change the code to dump all the records to a List<T> first, then add the lists at the end:

[TestMethod]
public void InsertEfAddRangeTest()
{      
    var context = new WebMonitorContext();

    var users = new List<User>();
    var sites = new List<WebMonitorSite>();

    for (int i = 0; i < 10000; i++)
    {        
        …
        users.Add(user);

        …
        sites.Add(site);
    }

    context.Users.AddRange(users);
    context.Sites.AddRange(sites);

    context.SaveChanges();
}

Note that here I’m not turning off AutoDetectChangesEnabled and the performance is in the 12 range which is provides almost identical performance to when AutoDetectChangesEnabled is off.

Realistically this only works if the set doesn’t get too large since this requires that the whole set of data is loaded into memory first. If you need to do multiple batches (combined with code similar to the Batching section) you also still would need to disable AutoDetectChangesEnabled=false.

Entity Framework

I’ve been slow to come around to accepting Entity Framework, since my early experiences with Version 1.0 really left a bad taste in my mouth. But since Version 4.0 and Code First, I’ve been using EF more and more. It’s come a long way and performance and stability at least close to raw ADO.NET perf in most cases. And for those few things that don’t work or are too slow I tend to fall back to ADO.NET as necessary using my data tools I’ve grafted onto the DbContext for easy access. This seems to be working well for me these days and I’ve had good results on the last 3 projects I’ve worked on as well as a few in-house projects.

But Entity Framework has its quirks as any ORM tool does, and there are lots of things that are found and learned the hard way by doing it wrong first, then finding a solution that doesn’t quite feels satisfying, because the original use case should have worked. This is one of them, but it’s minor. Hopefully this post will save somebody some grief when they search for this particular problem.

Love in coding...