locked
Out of memory Exception RRS feed

  • Question

  • When I add 500'000 records (i "savechanges" every 500) in a table using entity framework, the system is crashing with the exception "Out of Memory".

    is it possible to unload objects ? (500'000 records in table1 are linked to one record in table2)

    my software is crashing and i m on the edge of rewriting it and forget all about entity framework :(

    steve

     


    Steve
    Thursday, July 28, 2011 1:52 PM

Answers

  • Half a million records is a lot to load in.

    You would be better off issuing one UPDATE command to the database and updating all of them on mass, if thats possible.

     

    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Thursday, July 28, 2011 2:43 PM
  • Hi Stivo;

    Please note that Linq to EF as well as Linq to SQL both do a single trip to the server for each update in the object context so if you have 500 added / modified records and you do a SaveChanges that will produce 500 individual SQL commands being sent to the server. If you have that many inserts / changes I would do as Steve suggested.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Thursday, July 28, 2011 5:11 PM
  • Dear Stivo

    I've got the same problem of adding too many record to the database. My app should create a demo database with 100,000 patients, and 4-8 phone records for each one!!... and i have then folowing notes:

    1. I never get Out of Memory exception in updating methods.... the most common case for this exception in my little experience is circular calls.... like Property1 changes Property2 which again changes Property1 and so on.... make sure you don't have this issue.
    2. Using DataCommands with stored procedures are much faster and uses less memory.
    3. Using cursors is even much faster!!.
    4. After all, you may need to use EF which will cost allot of memory (400 MB in my app), Here is my solution to over come this:
    • create a "lst As List (Of Object)" to keep the created objects inside it.
    • When ever you create an entity object add it to that list.
    • Call SaveChanges when you think it is time for that(Calling a that after adding a large number of entities will cause you app to hang for a long time).
    • For each item in your list (lst) call the object context method Detach
    • After you finish the for each loop clear the lst and make sure that the items inside it are not used any where else.
    • You can call the static method GC.Collect if you like.

    As our freinds said before it is better to use direct Update command for that, But in some cases the steps above can help a little.

    Best Regards.



    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Tuesday, August 2, 2011 7:29 PM
  • Hi,

    I was 3 weeks on holiday... back to work now...

    concerning the problem, i changed the code to import my flat files by executing SQL updates instead of using entity framework. now it's ok.

    Steve


    Steve
    • Marked as answer by Alan_chen Tuesday, August 9, 2011 6:20 AM
    Tuesday, August 9, 2011 6:11 AM

All replies

  • Half a million records is a lot to load in.

    You would be better off issuing one UPDATE command to the database and updating all of them on mass, if thats possible.

     

    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Thursday, July 28, 2011 2:43 PM
  • Hi Stivo;

    Please note that Linq to EF as well as Linq to SQL both do a single trip to the server for each update in the object context so if you have 500 added / modified records and you do a SaveChanges that will produce 500 individual SQL commands being sent to the server. If you have that many inserts / changes I would do as Steve suggested.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Thursday, July 28, 2011 5:11 PM
  • Hi,

    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.

    Tuesday, August 2, 2011 3:58 PM
  • Dear Stivo

    I've got the same problem of adding too many record to the database. My app should create a demo database with 100,000 patients, and 4-8 phone records for each one!!... and i have then folowing notes:

    1. I never get Out of Memory exception in updating methods.... the most common case for this exception in my little experience is circular calls.... like Property1 changes Property2 which again changes Property1 and so on.... make sure you don't have this issue.
    2. Using DataCommands with stored procedures are much faster and uses less memory.
    3. Using cursors is even much faster!!.
    4. After all, you may need to use EF which will cost allot of memory (400 MB in my app), Here is my solution to over come this:
    • create a "lst As List (Of Object)" to keep the created objects inside it.
    • When ever you create an entity object add it to that list.
    • Call SaveChanges when you think it is time for that(Calling a that after adding a large number of entities will cause you app to hang for a long time).
    • For each item in your list (lst) call the object context method Detach
    • After you finish the for each loop clear the lst and make sure that the items inside it are not used any where else.
    • You can call the static method GC.Collect if you like.

    As our freinds said before it is better to use direct Update command for that, But in some cases the steps above can help a little.

    Best Regards.



    • Marked as answer by Alan_chen Monday, August 8, 2011 11:03 AM
    Tuesday, August 2, 2011 7:29 PM
  • Hi,

    I was 3 weeks on holiday... back to work now...

    concerning the problem, i changed the code to import my flat files by executing SQL updates instead of using entity framework. now it's ok.

    Steve


    Steve
    • Marked as answer by Alan_chen Tuesday, August 9, 2011 6:20 AM
    Tuesday, August 9, 2011 6:11 AM