0

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:

  1. bulk: cpu (31), reads(1809), duration(29)
  2. 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:

  1. Why is there so much overhead when doing inserts with EF? How can this be reduced?
  2. 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

Søren D. Ptæus
  • 4,176
  • 1
  • 26
  • 28
Josef Biehler
  • 952
  • 4
  • 16
  • I don't see how that "BulkInsert" would help, if you still have to "SaveChanges" (this is where the actual call to the SQL server will happen, "Customer.Add" does only add it to the in-memory DbSet. This question has some insights: https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework?rq=1 – johannes.colmsee May 18 '18 at 06:57
  • Another thing - either use transaction for both your tests, or none. Transaction *will* slow down the insert on the SQL-Server for sure. – johannes.colmsee May 18 '18 at 06:58
  • According to the webpage of the nuget package you are using - you shouldn't need to call SaveChanges (their "howto" does not...) – johannes.colmsee May 18 '18 at 07:01
  • I've added the Transaction because i thought that it might speed up because SQL server can use this transaction instead of creating one on its own. The single insert needs about 130ms without transaction, too. The "SaveChange" within the BulkInsert should not do something. The profiler does not show anyrthing while running over the SaveChanges(). I have just added it to make both variants more compareable. – Josef Biehler May 18 '18 at 08:11
  • I just found this this article you have linked and agree with it that BulkInsert is much faster when having a bunch of data. But in my special case i see in the profiler that the BulkInsert takes more time than the single insert of one row. But this performance difference does not show up when executiong the code. And the question is, do i have any changes to overcome the EF overhead that might lead to the extremly slowly inserts. It is also noteable that if the insert of a single row takes 130ms every time, I should use BulkInsert in the whole application! – Josef Biehler May 18 '18 at 08:17

0 Answers0