none
EF Bad Performance with DBContext and SaveChanges RRS feed

  • Question

  • Hi guys,

    I'm working with Entity Frameworks on a Sqlserver Database. My database has about  40 tables. I try to add 10000 new entities in my model.

    I have a foreach construct and in every step I create a new element and call SaveChanges() function from my DbContext object. It works fine but after 200 elements, it becomes slow and slow. I would like to do something like:

    a) clear the context or the connection, every for example 100 new elements.
    b) remove the created objects that I don't need (clean the context)
    c) .... ???

    How can I do it with a better performance? Do you have an example code? The problem is so basic but so difficult to find a (good) solution.

    Thank you in advanced

    Tuesday, March 5, 2013 3:13 PM

Answers

  • Hi MrSiggi;

    Entity Framework does not perform well to insert 10000 records. You are better off doing the inserts using SqlBulkCopy. See this link SqlBulkCopy for Generic List<T> (useful for Entity Framework & NHibernate).

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, March 5, 2013 5:09 PM
  • Hi,

    As Fernando said, EF is often not the best tool for doing bulk inserts. We intend to make it able to perform better when doing them in the future, but we aren't working on it right now.

    As to making EF as fast as it can, change tracking can effect the performance of inserts as described here: http://blog.staticvoid.co.nz/2012/5/7/entityframework_performance_and_autodetectchanges. Turning off change detection as you do the large insert could help performance significantly.

    It sounds like change tracking is probably the reason for the steadily decreasing performance, as EF is getting more and more objects that it is checking.

    It should also be possible to dispose of and re-create the context periodically, every n number of rows for example. The expense of creating the context and executing the first query is cached at the app domain level. So disposing and re-creating a context inside the same app domain is usually fairly cheap.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Tuesday, March 5, 2013 6:48 PM
    Moderator

All replies

  • Hi MrSiggi;

    Entity Framework does not perform well to insert 10000 records. You are better off doing the inserts using SqlBulkCopy. See this link SqlBulkCopy for Generic List<T> (useful for Entity Framework & NHibernate).

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, March 5, 2013 5:09 PM
  • Hi,

    As Fernando said, EF is often not the best tool for doing bulk inserts. We intend to make it able to perform better when doing them in the future, but we aren't working on it right now.

    As to making EF as fast as it can, change tracking can effect the performance of inserts as described here: http://blog.staticvoid.co.nz/2012/5/7/entityframework_performance_and_autodetectchanges. Turning off change detection as you do the large insert could help performance significantly.

    It sounds like change tracking is probably the reason for the steadily decreasing performance, as EF is getting more and more objects that it is checking.

    It should also be possible to dispose of and re-create the context periodically, every n number of rows for example. The expense of creating the context and executing the first query is cached at the app domain level. So disposing and re-creating a context inside the same app domain is usually fairly cheap.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Tuesday, March 5, 2013 6:48 PM
    Moderator
  • If there are more than 1000 records then SqlBulkCopy using DataTable() is the way to go. It's super fast and its storing 100K records in half a second, whilst EF took 2.5mins. Thanks for the ready made method
    Thursday, April 20, 2017 1:41 PM