none
Update a large amout of data in EF RRS feed

  • Question

  • I need to load a lot of data records from database with entity framework and then apply changes to them and save back to database. All of the update need to be in one transaction.

    My application use 680 M of memory after loaded data records and updated in memory. When I call ObjectContext.SaveChanges() the memory usage surges to 1.5 G and I get a OutOfMemoryException. Why ObjectContext.SaveChanges call will more than double the memory usage?

    I then split the update into small pieces. I do load, update and save for each piece. After that, the memory for the piece is detached from the object context. The memory usage drops down a lot (max 270 M). However, in mid of the process, I got an error as following when calling ObjectContext.SaveChanges() for the data piece

    {"The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements."}

     

    Why my transaction is completed while I still have more pieces of data to load and update before calling to TransactionScope.Complete?


    Roy
    Tuesday, July 12, 2011 2:55 PM

Answers

  • Hi again,

    Yes, it will.. You will only get transaction within a context to work this will not transfer across the contexts. I'm however not sure if this will help you.

    Example:

    Run this for each piece of data

    using(MyContext context = new MyContext())
    {
        //Do the work for one piece of the data
        .......

        //call SaveChanges
        context.SaveChanges(SaveOptions.None);
    }

    BTW: As you see here, did you try in your code to call SaveChanges with SaveOptions.None in your initial code? This may both speed up and reduce memory consumption on SaveChanges.

    My suggestion is still that you refactor your code to run this large batch update of data on the database server using a stored procedure both for speed and memory management.


    --Rune
    Tuesday, July 12, 2011 7:17 PM

All replies

  • Hi,

    Using EF is very inefficient in this type of work. It's also not very efficient to move this amount of data from your database server to your .NET code anyway. I would recommend you to (if possible) reconsider and use a stored procedure to change the data and instead call this stored procedure with parameters that is needed to do the modifications.

    If this isn't possible.... I'm actually not sure why you get this error, but you could try to dispose your context after your savechanges between each batch, and see if this helps you get rid of the transaction exception too.


    --Rune
    Tuesday, July 12, 2011 5:01 PM
  • Hi Rune,

    I am a little confused for "try to dispose your context". The transaction is associated with the ObjectContext. If I dispose it, the transaction will be ended as well. Am I right? Or I misunderstand what you said here?

    Thanks.


    Roy
    Tuesday, July 12, 2011 6:57 PM
  • Hi again,

    Yes, it will.. You will only get transaction within a context to work this will not transfer across the contexts. I'm however not sure if this will help you.

    Example:

    Run this for each piece of data

    using(MyContext context = new MyContext())
    {
        //Do the work for one piece of the data
        .......

        //call SaveChanges
        context.SaveChanges(SaveOptions.None);
    }

    BTW: As you see here, did you try in your code to call SaveChanges with SaveOptions.None in your initial code? This may both speed up and reduce memory consumption on SaveChanges.

    My suggestion is still that you refactor your code to run this large batch update of data on the database server using a stored procedure both for speed and memory management.


    --Rune
    Tuesday, July 12, 2011 7:17 PM
  • Hi Roy,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 18, 2011 3:57 AM
    Moderator