Improving time it takes to insert thousands of records using Entity Framework

I have a task in my program that is inserting thousands (94,953 in one instance and 6,930 in another) of records into my database using Entity Framework.

Right now I am doing this and calling the .Add() method for each record but it takes about 1 minute to insert the smaller batch and over 20 minutes for the larger batch. I have tried the .AddRange() method but that jumped the smaller batch to over 4 minutes.

Is there another approach with Entity Framework 6 or is this a limitation I have to live with? If it makes any difference, the data is going into a SQL Server 2012 R2 instance.

var taskCodes = DynamicsHelper.GetTaskCodes(dynamicsSession, "01-01-1990");
        Console.WriteLine("Adding task codes to database.");

        using (var db = new JobSightDbContext())
        {
            foreach (var taskCode in taskCodes)
            {
                var projectID = db.ProjectCodes.Where(project => project.Code == taskCode.Item4).Select(project => project.ID).FirstOrDefault();

                if (projectID != 0)
                {
                    var newTaskCode = new TaskCode()
                    {
                        Code = taskCode.Item1,
                        Description = taskCode.Item2,
                        IsActive = taskCode.Item3,
                        ProjectID = projectID
                    };

                    db.TaskCodes.Add(newTaskCode);
                    db.SaveChanges();
                }
            }

            Console.WriteLine("{0} tasks added to the database.", db.TaskCodes.Count());
        }

Since you're not making any changes to existing objects, you might set the AutoDetectChangesEnabledproperty of your context to false.

From MSDN:


Gets or sets a value indicating whether the DetectChanges method is called automatically by methods of DbContext and related classes. The default value is true.

Calling the Add calls the DetectChanges method every time and is expensive. Turn it off and at the end, turn it on again.

Example:

using (var db = new DbContext)
{
    try
    {
        db.Configuration.AutoDetectChangesEnabled = false;
        //logic
    }
    finally
    {
        db.Configuration.AutoDetectChangesEnabled  = true;
    }
}

More reading on this: Secrets of DetectChanges Part 3

Also, you call the SaveChanges method every iteration in your loop. This means you make a call to your DB every iteration to persist that entity and is intensive and time consuming. Place that line of code outside your loop.

foreach (var taskCode in taskCodes)
{
    //logic
    db.TaskCodes.Add(newTaskCode);
}

db.SaveChanges();

It's much more performant to make 1 call for many items than many calls for 1 item!

Here's an example question on StackOverflow about this: Any difference between calling SaveChanges() inside and outside a foreach loop?

StackExchange Code Review Q#130070, answer score: 9