none
Load child table with ONLY related records from Parent with one server round trip. RRS feed

  • Question

  • Problem is that I have two large tables (orders of millions of rows).  I have devised a select query that will retrieve ONLY the parent rows of interest into an ADO.net datatable.

    Once this selection is done and I have the datatable filled with the desired records, the number of child records becomes 'reasonable', so  I want to then load ONLY the child records from the sql server database.

    So far all the examples I have found use some form of :

    "For each record in the parent table load the records for the children."

    I'm trying to get this done with only one round trip to the server, but alas, the more I look the more confused I become between all the technologies out there. 

    Any insight, examples, pointers to examples would be very greatly apprecieated.

    Ed Warren


    Ed Warren Raising sails on masts not masts on sails

    Tuesday, February 5, 2013 1:03 PM

Answers

  • Well, fetching large amount of data in one trip vs fetching many times in many trips should be tested before choosing one them..

    Here is a linq of how I will do it

    let say you have datatable dt_list_of_codes which contain the 1-3K values the users gets by query

    Dim db As New myDataContext
    
    Dim in_values = From t In dt_list_of_codes.AsEnumerable Select t("id")
    
    Dim result = From t In db.orders Where in_values.Contains(t.id) Select t
    
    
    Note: I don't test the limitation of in_values
      




    .Net Blog VFP Blog

    • Marked as answer by Edward Warren Wednesday, February 20, 2013 12:10 PM
    Wednesday, February 20, 2013 11:40 AM

All replies

  • Ed,

    Are you also updating this and is it a multi user application, then forget it, you create only more round-trips with not used traffic.


    Success
    Cor

    Tuesday, February 5, 2013 3:14 PM
  • The database is used as a read only source.  I download the data then present it in a UI.  Also a single user application.

    Ed Warren


    Ed Warren Raising sails on masts not masts on sails

    Tuesday, February 5, 2013 4:17 PM
  • Than you can fill the datatables using a DataReader.

    I've updated a Visual Basic sample on our website which was still in a kind of framework 1.1 format. 

    http://www.vb-tips.com/dbpages.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53

    It gives this page as result. You can set your retrieving of the table in a for each loop from the other table.

    I don't have an C# sample but if you want that, then reply, I know Bonny has endless of those using a datareader.


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, February 6, 2013 9:28 AM
    • Proposed as answer by Alexander Sun Thursday, February 14, 2013 2:53 AM
    Wednesday, February 6, 2013 9:15 AM
  • Thanks that will work, but I am trying to avoid the stepping through the parent table one row at a time, in my application the average number of parent rows will be in the order of 2000-4000 records.  I would really like to get the child records loaded with only one sql roundtrip.

    However, I will give your code a try and see what performance I can get out of it.

    Again, thanks for your kind help.


    Ed Warren Raising sails on masts not masts on sails

    Wednesday, February 6, 2013 12:07 PM
  • Problem is that I have two large tables (orders of millions of rows).  I have devised a select query that will retrieve ONLY the parent rows of interest into an ADO.net datatable.

    You said you have two large tables Orders which is millions of rows, the other table is what? OrderDetail?

    The parent rows of interest, how you are getting those?


    Blog: http://samir-ibrahim.blogspot.com/

    Tuesday, February 19, 2013 2:36 PM
  • My first table (list of codes) has about 2 million rows (Key Field ID).  The user does a query to return a set of rows (approximately 1k rows).  Then from a related child table (details, fk ID) which has on the order of 5-8 million rows I need to select only rows that match the first set from the list of codes (on the order of 2-3 thousand records).  I could build a temp table in the database then do a join query, but I'm looking for a more elegant solution, e.g. using linq to sql or linq to objects.  Since I have returned MyCodeList as a list(of Codes) object, it would be nice to be able to query for the children w/o stepping through the list.

    Hope that explains it better, thanks for your interest.


    Ed Warren Raising sails on masts not masts on sails

    Tuesday, February 19, 2013 2:50 PM
  • Well, fetching large amount of data in one trip vs fetching many times in many trips should be tested before choosing one them..

    Here is a linq of how I will do it

    let say you have datatable dt_list_of_codes which contain the 1-3K values the users gets by query

    Dim db As New myDataContext
    
    Dim in_values = From t In dt_list_of_codes.AsEnumerable Select t("id")
    
    Dim result = From t In db.orders Where in_values.Contains(t.id) Select t
    
    
    Note: I don't test the limitation of in_values
      




    .Net Blog VFP Blog

    • Marked as answer by Edward Warren Wednesday, February 20, 2013 12:10 PM
    Wednesday, February 20, 2013 11:40 AM
  • Thanks Samir, that looks like it will work.  At least, to date, it is the best answer.


    Ed Warren Raising sails on masts not masts on sails

    Wednesday, February 20, 2013 12:11 PM
  • You are welcome Edward.

    Glad I helped.


    .Net Blog VFP Blog

    Wednesday, February 20, 2013 12:19 PM