Improve entity framework insert

Question

I have been dealing with this for 2 days and I can't find solution.

using (TaxablePersonContext context = new TaxablePersonContext(this.ConnectionString))
{
    context.Configuration.AutoDetectChangesEnabled = false;
    foreach(TaxablePerson p in persons) // Persons has always size 1000
    {
      // TaxablePerson has some other properties e.g. Name, VatId, Street,...
      p.RecCreatedBy = "application name";
      p.RecCreatedOn = this.SynchronizationStartDateTime;
      p.RecModifiedBy = "application name";
      p.RecModifiedOn = this.SynchronizationStartDateTime;
      p.RecSyncDate = this.SynchronizationStartDateTime;
      p.RecActive = true;
    }
    DateTime start1 = DateTime.Now;
    context.TaxablePersons.AddRange(persons);
    TimeSpan end1 = DateTime.Now.Subtract(start1);

    DateTime start2 = DateTime.Now;
    context.SaveChanges();
    TimeSpan end2 = DateTime.Now.Subtract(start1);
}

I takes nearly 10 seconds to insert 1000 records and 98 seconds to insert 10.000 records in sql server. Can you please advise what to do to improve Entity framework insert performance. I read this post Fastest Way of Inserting in Entity Framework and included the tips mentioned in this post, but still insert is very slow. I need to insert 260.000 records which takes 52 minutes. I'm inserting in batches of 1000, which upper code demonstrates. Data is read from file, when I hit 1000 records I do synhronization with database. What else can I do? Some people mention that when used setting context.Configuration.AutoDetectChangesEnabled = false; performance improved from minutes to nearly some seconds. What am I missing? I'm using entity framework 6.1.3.

You should also use: context.Configuration.ValidateOnSaveEnabled = false; — K.J., Sep 22 '15 at 12:05
@K.J. Thank you for setting but it does not make a difference. — broadband, Sep 22 '15 at 12:08
add this code after `using` : `context.Database.Log = s => Debug.Write(s);` and check Output window in VS at the end of `using`. Probably you'll see something weird — Szer, Sep 22 '15 at 12:10
@broadband, the best solution I've came to is **not to use** EF for this scenario. [dapper](https://github.com/StackExchange/dapper-dot-net) + stored procedure + [table valued parameters](https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) gives amazing performance. — xZ6a33YaYEfmv, Sep 22 '15 at 12:22
you may try bulk insert instead of AddRange https://efbulkinsert.codeplex.com/ — Chandrasekar Kesavan, Sep 22 '15 at 12:37
@BrandonSeydel performance is the same if I change _blockSize to 100. I tried with 10, 100, 1000 and 5000 and the time when syncing 10.000 is the same. — broadband, Sep 22 '15 at 13:44
@Chandru does efbulkinsert support update, because I need to update as well. — broadband, Sep 22 '15 at 13:45
@ieaglle totally agree with you. When dealing with large inserts/updates using ORM is not a good idea. — broadband, Sep 22 '15 at 13:47
I am sure that you are doing it incorrectly if it is still that slow....Parallel.For groups of 100 use new context inside....10 seconds for 10000 — Brandon Seydel, Sep 22 '15 at 20:24
@BrandonSeydel I'm not sure I understand you correctly. I'm already doing new context for every 100 (by setting blockSize to 100). When I read 100 records from file I sync them with database i.e. upper code is executed (List persons contains 100 entries) — broadband, Sep 23 '15 at 07:31

score 0 · Answer 1 · answered Sep 25 '15 at 12:58

Using sql profiler, I discovered that Entity framework sends queries one by one e.g.

INSERT INTO MyTable (id, name) VALUES (1, 'Bob')
INSERT INTO MyTable (id, name) VALUES (2, 'Peter')
INSERT INTO MyTable (id, name) VALUES (3, 'Joe')

So actually sql server in this case executes 1000 inserts very slow - nearly 10 seconds (although the execute in transaction). I then constructed insert with multiple values - SQL with many values and insertion of 1000 records took 5 seconds (50% better - earlier 10 seconds). Sql server has a limit of sql parameters you can pass, which is 2100, so this the best you can do with this approach.

private void MultiRecordsInsert(TaxablePersonContext context, List<TaxablePerson> personsToAdd)
{
  List<SqlParameter> parameters = new List<SqlParameter>();
  string firstQuery = @"insert into TaxablePerson (c1, c2, c3) values ";
  string query = firstQuery;

  for (int i = 0; i < personsToAdd.Count; i++)
  {
    query += "(@c1" + i.ToString();
    query += ",@c2" + i.ToString();
    query += ",@c3" + i.ToString() + "),";
    parameters.Add(new SqlParameter("@c1" + i.ToString(), personsToAdd[i].c1));
    parameters.Add(new SqlParameter("@c2" + i.ToString(), personsToAdd[i].c2));
    parameters.Add(new SqlParameter("@c3" + i.ToString(), personsToAdd[i].c3));

    // table has 16 columns (I reduced here for simplicity) so: 2100 / 16 = 131, 
    // used 100
    //
    if (i % 100 == 0)
    {
      query = query.Substring(0, query.Length - 1); // remove last comma
      context.Database.ExecuteSqlCommand(query, parameters.ToArray());

      query = firstQuery;
      parameters = new List<SqlParameter>();
    }
  }

  if (parameters.Count > 0) // what is left
  {
    query = query.Substring(0, query.Length - 1);
    context.Database.ExecuteSqlCommand(query, parameters.ToArray());
  }
}

consider using [TVP](https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx)s. there is no limit I'm aware of. — xZ6a33YaYEfmv, Sep 25 '15 at 13:00

Improve entity framework insert

1 Answers1