Entity framework performance issue, saveChanges is very slow

Question

Recently, I am doing some simple EF work. Very simple, first,

List<Book> books = entity.Books.WHERE(c=>c.processed==false)ToList();   then       foreach(var book in books)       {         //DoSomelogic, update some properties,        book.ISBN = "ISBN " + randomNumber();       book.processed = true;       entity.saveChanges(book)       }   

I put entity.saveChanges within the foreach because it is a large list, around 100k records, and if this record is processed without problem, then flag this record, set book.processed = true, if the process is interrupted by exception, then next time, I don't have to process these good records again.

Everything seems ok to me. It is fast when you process hundreds of records. Then when we move to 100k records, entity.saveChanges is very very slow. around 1-3 seconds per record. Then we keep the entity model, but replace entity.saveChanges with classic SqlHelper.ExecuteNonQuery("update_book", sqlparams). And it is very fast.

Could anyone tell me why entity framework process that slow? And if I still want to use entity.saveChanges, what is the best way to improve the performance?

Thank you

Answer 1

Turn off change tracking before you perform your inserts. This will improve your performance significantly (magnitudes of order). Putting SaveChanges() outside your loop will help as well, but turning off change tracking will help even more.

using (var context = new CustomerContext()) {     context.Configuration.AutoDetectChangesEnabled = false;      // A loop to add all your new entities      context.SaveChanges(); } 

See this page for some more information.

Answer 2

I would take the SaveChanges(book) outside of the foreach. Since book is on the entity as a list, you can put this outside and EF will work better with the resulting code.

The list is an attribute on the entity, and EF is designed to optimize updates/creates/deletes on the back end database. If you do this, I'd be curious whether it helps.