We have a piece of code that makes a bulk insert using the lib EntityFramework.BulkInsert-ef6-ext. We are facing some performance issues: The whole process of doing some work + inserting data takes about 150ms. That seems a lot as it was on my developer machine where only I'm working. I assume that on our production server this process is a lot slower as there is many traffic on it.
My first assumption was that the bulk insert is slower as single inserts when having only a few datarows.
I set up a small test app that traces the time during insert. Here are my results for 1 row:
Profiler:
- bulk: cpu (31), reads(1809), duration(29)
- single: cpu(0), reads(13), duration(1)
But looking at the traced time in my c# code shows a completly different picture:
bulk: 85ms
single: 130ms
Now two questions arise:
- Why is there so much overhead when doing inserts with EF? How can this be reduced?
- Is there a possibility to speedup single inserts with EF?
Finally here is my test code:
static void Main(string[] args)
{
var sw = new Stopwatch();
long t1, t2 = 0;
using (var ctx = new TestContext())
{
var list = new List<Customer>();
for (int i = 0; i < 1; i++)
{
list.Add(new Customer());
}
sw.Start();
ctx.BulkInsert(list);
ctx.SaveChanges();
}
sw.Stop();
t1 = sw.ElapsedMilliseconds;
sw.Reset();
using (var ctx = new TestContext())
{
var trx = new TransactionScope();
ctx.Configuration.LazyLoadingEnabled = false;
ctx.Configuration.AutoDetectChangesEnabled = false;
ctx.Configuration.ValidateOnSaveEnabled = false;
var list = new List<Customer>();
for (int i = 0; i < 1; i++)
{
ctx.Customer.Add(new Customer());
}
sw.Start();
ctx.SaveChanges();
trx.Complete();
trx.Dispose();
}
sw.Stop();
t2 = sw.ElapsedMilliseconds;
}
Best regards, Josef