none
out of memory RRS feed

  • Question

  • Hello,
    C# is using the datareader to get 100 million rows and do some validation for every row.
    It fails with out of memory error.
    I wonder to solve this I should do the validation for each row in batches i.e.

    1- get 100 million rows into the datatable

    2- using a datareader, go through the first 100 thousand and do the validation

    3- clear datareader

    4- re-populate datareader with the next 100 thousand rows and do validation

    5- clear datareader

    ...

    until all rows in datatable is validated.

    any suggestions please?

    thanks

     

    • Moved by Kevin Linq Tuesday, September 13, 2016 1:39 AM
    Sunday, September 11, 2016 3:54 PM

Answers

  • >>any suggestions please?

    Don't select 100 million rows up front. Select 1000 or something rows and validate these, one-by-one if you can, and then do same thing over and over again 100,000 times (?) in a loop. Trying to load 100 million rows into a memory at once is doomed to fail.

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Sunday, September 11, 2016 8:27 PM

All replies

  • With a DataReader, you can read one row at a time ... couldn't you just do your validation one row at a time then? Of course, given the time that it's going to take to read each row and validate each row one at a time, you probably need a pretty big timeout setting for both your database connection and command.

    Could this validation, whatever it is, be something that can be done in the database itself? Like maybe handled by UPDATE and INSERT triggers? This would be better, depending on what you need to do for your validation.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 11, 2016 4:34 PM
  • Do it at the server side with an SQL transact string

    Success
    Cor

    Sunday, September 11, 2016 4:37 PM
  • >>any suggestions please?

    Don't select 100 million rows up front. Select 1000 or something rows and validate these, one-by-one if you can, and then do same thing over and over again 100,000 times (?) in a loop. Trying to load 100 million rows into a memory at once is doomed to fail.

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Sunday, September 11, 2016 8:27 PM
  • Hi  arkiboys :

    Thank you for posting here.

    Since your issue is related to ADO.Net , I will move your thread to ADO.net datasetforum . 

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Here is the link of ADO.net forum:https://social.msdn.microsoft.com/Forums/en-US/home?category=dataplatformdev

    If you have anything else regarding C# programming , please feel free to let me know . 

    Best Regards,

    Kevin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.


    • Edited by Kevin Linq Tuesday, September 13, 2016 1:38 AM
    Tuesday, September 13, 2016 1:37 AM
  • Hi arkiboys,

    You could also put the records into a dictionary by using ORM tools, for more information, please refer to:

    http://stackoverflow.com/questions/13532800/loading-multiple-large-ado-net-datatables-datareaders-performance-improvements

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 13, 2016 4:50 AM
    Moderator
  • Hi,

    you are getting your data from SQL, what validation do you need to do that is not possible in SQL?

    Note: if all the data that you need to validate is on the SQL database, SQL can do the validation; don't underestimate SQL; it'll be MUCH faster too.

    Regards,

    Nico


    Regards, Nico <br/>


    • Edited by Nico Boey Friday, September 16, 2016 9:57 AM
    Friday, September 16, 2016 9:56 AM