none
fill a data table from a huge IEnumerable takes a long time RRS feed

  • Question

  • Hi

    I use a data table to pass it as a parameter of a stored procedure. To fill the data table, I read a huge list saved in IEnumerable  (items, Contains millions of record) as below: 

    foreach (Model userJobPostMatch in items)            {
    
      dataTable.Rows.Add();
    
    }

    Then pass the dataTable to the stored procedure. But it takes a long time to add rows to dataTable. What is the optimized way to do that? is it possible to use multi-threading  ? 

    Sunday, February 18, 2018 8:37 AM

All replies

  • Hi persian developer,

    >>Then pass the dataTable to the stored procedure. But it takes a long time to add rows to dataTable. What is the optimized way to do that? is it possible to use multi-threading  ? 

    I don't suggest that you load a huge data from database, you could consider to handle paging and in your case.  about ado.net stored procedure with paging, please refer to:

    https://www.codeproject.com/Articles/590341/Stored-Procedure-with-Sorting-Paging-and-Filtering

    In addition, you could load data into datatable instead of IEnumerable, please refer to the following link.

    https://stackoverflow.com/questions/13402003/how-to-populate-a-datatable-from-a-stored-procedure

    I don't think we could use multi-threading, because with multi-threading, we could not know what records have been added into the datatable.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 22, 2018 1:55 AM
    Moderator
  • Persian Developer, 

    It seems you are searching for the slowest way. 

    Yes to do it as slow as possible avoid the DBDataAdapther which is special made to fill rows from a database into a dataadapter. 

    But beside the way you use now there are few other ways to slow it more down. Of course will using more threads for this one command operation slow it more down. You can then tell "It is a very difficult process". A sentence seen by professionals as "He does not manage the process"

    If I misunderstood you and you want to let it go smooth, use a DBDataAdapter (we cannot see what database you use) and set the SelectCommand.Text to the SP and the CommandType to SP. 

    By the way your text a huge list is also not qualifying you as professional. A professional makes it so small as possible by using a where clause.  


    Success
    Cor

    Thursday, February 22, 2018 3:10 PM
  • You guys (@Zhanglong and @Cor) are both missing the point. What @persian is asking about is *not* loading a DataTable from a database. He's *passing* the DataTable as a parameter to the Stored Procedure.

    @persian ... not knowing what your Stored Proc is doing, I don't know if you can pass only a chunk of the DataTable at a time. For example, if your Stored Proc is just using the DataTable parameter to insert the data into a database table, then you should be able to only send part of the data at a time. If you *can* do that, then break down the filling of the DataTable into manageable chunks (like maybe 10,000 rows at a time, or something like that) and call your Stored Procedure multiple times.

    However, that will still take a while unless you can do it Asynchronously. If you can chunk it like this, then perhaps an Asynchronous way to do this, might help. Kind of a "Fire and Forget" kind of thing. Take a look at my blog post, to see if might help:

    https://geek-goddess-bonnie.blogspot.com/2017/03/fire-and-forget.html


    ~~Bonnie DeWitt [C# MVP]

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

    Saturday, February 24, 2018 6:05 PM
  • Yes @BonnieB you got my problem. I fill the datatable from an IEnumerable not the database. I will take your solution and let you know. 
    Sunday, February 25, 2018 1:04 PM
  • Hi persian developer,

    Is there any update? Do you resolve the issue?

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 1, 2018 5:10 AM
    Moderator
  • Hey @Persian Developer ... it's been 3 weeks since you were last here on this thread. Have you resolved your problem? If so, let us know how you solved it!

    ~~Bonnie DeWitt [C# MVP]

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


    • Edited by BonnieBMVP Sunday, March 18, 2018 5:00 PM
    Sunday, March 18, 2018 4:58 PM