EF 6 performance while updating multiple records with different values in same table

Question

I have a table , whose values are updated conditional basis, and when I am calling

db.SaveChanges()

there is a huge performance drop.

I am also setting properties

db.Configuration.AutoDetectChangesEnabled = false;                  
db.Configuration.ValidateOnSaveEnabled = false;

still results are not as expected.

Edit 1:

using(var db= new MyEntities())
{
  db.Configuration.AutoDetectChangesEnabled = false;                  
  db.Configuration.ValidateOnSaveEnabled = false;

 foreach(var acc in myacclist)
 {
    //will update my account objects here
 }
  db.SaveChanges();
}

you might want to share the surrounding code with us. SaveChanges() does a lot of things, whereas the performance of this mostly depends on the internal context state and connection handling. — DevilSuichiro
– DevilSuichiro, Commented Jun 6, 2016 at 8:46
It will have more than 10000 accounts and at times more than 50k — Nagaraj
– Nagaraj, Commented Jun 6, 2016 at 10:43

Jonathan Magnan · Accepted Answer · 2016-06-06 18:44:53Z

1

Unfortunately, there is no way you will be able to have good performance with Entity Framework and SaveChanges.

SaveChange makes a database round-trip for every record update. So if you currently have 10,000 accounts, 10k database round-trip is performed.

Setting AutoDetectChangesEnabled and ValidateOnSaveEnabled is usually a very bad idea and will not really improve the performance since it the number of database round-trip the real issue.

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library allows to dramatically improve performance by performing:

BulkSaveChanges
BulkInsert
BulkUpdate
BulkDelete
BulkMerge

Example:

using(var db= new MyEntities())
{
    foreach(var acc in myacclist)
    {
        //will update my account objects here
    }

    db.BulkSaveChanges();
}

answered Jun 6, 2016 at 18:44

Jonathan Magnan

11.4k2 gold badges46 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nagaraj · Accepted Answer · 2016-06-07 09:06:17Z

0

Building a Update query in string builder and saving it for every 1k records improved my performance

using (var db = new MyEntities())
{
     StringBuilder finalquery = new StringBuilder();
     int i = 0;
     foreach (var acc in myacclist)
     {
       i++;
       //will update my account objects here
       finalquery.Append(stmnt);
       if (1 % 1000 = 0) { db.Database.ExecuteSqlCommand(finalquery.ToString()); }
      }

      db.SaveChanges();
 }

answered Jun 7, 2016 at 9:06

Nagaraj

2312 gold badges5 silver badges22 bronze badges

Collectives™ on Stack Overflow

EF 6 performance while updating multiple records with different values in same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related