CODEWITHSUNDEEP
c#entity-framework
Nagaraj
Nagaraj

Reputation: 231

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

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();
}

Upvotes: 0

Views: 1371

Answers (2)

Nagaraj
Nagaraj

Reputation: 231

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();
 }

Upvotes: 0

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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();
}

Upvotes: 1

Related Questions