Why is Entity Framework so slow to add multiple items in one SaveChanges()?

Viewed 4039

This is a follow-up from a previous question, in which I was trying to figure out the main cause for my code running slowly. I think I've narrowed it down to a minimal example below. I have a basic database structure as follows:

public class Foo
{
    public int Id { get; set; }
    public string Bar { get; set; }
}

public class FooContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
}

Now, if I had a list of Foo objects, and wanted to add them to the database, the suggested way would be to use AddRange(). But I noticed it was taking a long time, and is affected poorly by the number of items in the collection, even with a small amount like 200. So I wrote it out manually, and viola, it runs faster!

class Program
{
    static void Main(string[] args)
    {
        var foos = Enumerable.Range(0, 200).Select(index => new Foo { Bar = index.ToString() });

        // Make sure the timing doesn't include the first connection
        using (var context = new FooContext())
        {
            context.Database.Connection.Open();
        }

        var s1 = Stopwatch.StartNew();
        using (var context = new FooContext())
        {
            context.Foos.AddRange(foos);
            context.SaveChanges();
        }
        s1.Stop();

        var s2 = Stopwatch.StartNew();
        using (var context = new FooContext())
        {
            // Ignore the lack of sanitization, this is for demonstration purposes
            var query = string.Join(";\n", foos.Select(f => "INSERT INTO Foos ([Bar]) VALUES (" + f.Bar + ")"));
            context.Database.ExecuteSqlCommand(query);
        }
        s2.Stop();

        Console.WriteLine("Normal way: {0}", s1.Elapsed);
        Console.WriteLine("Hard way  : {0}", s2.Elapsed);
        Console.ReadKey();
    }
}

My initial thought was that Entity Framework might be using a separate transaction for each entry, but logging the SQL shows that's not the case. So why is there such a difference in execution time?

3 Answers
Related