Entity Framework Insert performance

In Entity Framework when you want to insert multiple items in your database you usually add your objects in your DbSet and then call context.SaveChanges at the end.

This action, you may think, performs bulk inserts in your db but in reality things are a bit different.

In the following example, we are going to generate multiple objects from our model `Products` and try to save them in our database (LocalDb) with different methods and see what actually is happening and which method is more performant.

1. Call SaveChages after each time a product is added in our dbContext

using (var dbContext = new ApplicationDbContext())
{
    for (var i = 0; i < _iterations; i++)
    {
        dbContext.Products.Add(new Product(Guid.NewGuid()));
        dbContext.SaveChanges();
    }
}

Iterations, Elapsed Time (ms)

10, 69

100, 1.269

1000, 10.450

10000, 210.752 (~3.5 mins)

2. Call SaveChanges once after adding all products in dbContext using AddRange

using (var dbContext = new ApplicationDbContext())
{
    var products = new List<Product>();
    for (var i = 0; i < _iterations; i++)
    {
        products.Add(new Product(Guid.NewGuid()));

    }
    dbContext.Products.AddRange(products);
    dbContext.SaveChanges();
}

Iterations, Elapsed Time (ms)

10, 49

100, 636

1000, 8.063

10000, 93.999 (~1.55 mins)

Monitoring Diagnostics Tools in Visual Studio 2015 you may see that records are inserted not in bulk but in multiple insert statements

3. Same as 2nd example but calling SaveChanges in Transaction

using (var dbContext = new ApplicationDbContext())
{
    using (var transactionScope = dbContext.Database.BeginTransaction())
    {
        for (var i = 0; i < _iterations; i++)
        {
            dbContext.Products.Add(new Product(Guid.NewGuid()));
        }

        dbContext.SaveChanges();
        transactionScope.Complete();
    }
}

Iterations, Elapsed Time (ms)

10, 80

100, 1.195

1000, 9.796

10000, 95.666 (~1.63 mins)

4. Use of BulkInsert

using (var dbContext = new ApplicationDbContext())
{
    var products = new List<Product>();
    using (var transactionScope = dbContext.Database.BeginTransaction())
    {
        for (var i = 0; i < _iterations; i++)
        {
            products.Add(new Product(Guid.NewGuid()));
        }
        dbContext.BulkInsert(products);

        dbContext.SaveChanges();
        transactionScope.Commit();
    }
}

Iterations, Elapsed Time (ms)

10, 52

100, 5

1000, 14

10000[attr class=”warning”], 157[attr class=”warning”] [/table]

Using Diagnostics Tools and Sql Profiler you may see that the BulkInsert operates resulting in a single insert bulk statement

BulkInsert is an extension for EntityFramework that supports EF4+.

You can download it through Nuget using:

Install-Package EntityFramework.BulkInsert-ef6

You can download the source code from here