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?
