What is the most effecient way to pull thousands of records with hundreds of thousands of child records into class objects?

Question

I have a scenario where I need to pull approximately 7500 database records where each records has 6 child entity lists. Each of those child lists could be 0 to approximately 125 records.

My class structure kind of looks like this:

public class Entity
{
    public int ID { get; set;
    public string Name { get; set; }
    public ICollection<ChildEntity1> Children1 { get; set; }
    public ICollection<ChildEntity2> Children2 { get; set; }
    public ICollection<ChildEntity3> Children3 { get; set; }
    public ICollection<ChildEntity4> Children4 { get; set; }
    ... 2 more collections
}

After I retrieve all of the Entities, I need to iterate over each one and perform some calculations, call some web services and other various things and ultimately export to a file.

What are the best strategies for retrieving this data from MS Sql Server 2008 using c# 4? Is a DataSet with DataAdapters the best way? ORM?

I want to stay away from select N+1 scenarios for obvious reasons.

Do you need all the entities in memory at the same time, or can you fetch a subset of entities, process them, and write them to a file? — Gilbert Le Blanc, Dec 08 '10 at 16:49
For one, your strategy should not be to "retrieve all of the entities" followed by "iterating over each one". Is there a reason your algorithm can't work on an as-you-go basis, one row at a time? — Kirk Woll, Dec 08 '10 at 16:51
@Gilbert- It would be possible to fetch a subset, say 100 at a time, process and add to a list to be exported at the end. The parent entity with its children are completely atomic. — Chris Conway, Dec 08 '10 at 17:04
@Kirk - working on an as-you-go basis would require me to hit the database ~45000 times (7500 parent records * 6 child collections). I guess that's the point of my question, is there a more efficient way? — Chris Conway, Dec 08 '10 at 17:07
@Chris, that's not what I meant. For example, if you simply use ADO.NET and get an IDataReader back, you call the `Read` method for each row. This involves only one hit to the database, but does not read the entire dataset into memory. — Kirk Woll, Dec 08 '10 at 17:21
@Kirk, right, but then I need to call the database to get the each of the child entity records (6 child entities per record) in the datareader. That's 4500 database calls when all is said and done. — Chris Conway, Dec 08 '10 at 17:48
@Chris, one query for the parents, one for the children. You can order the children by the parent id, so you should still be able to traverse them in one pass in sequence. — Kirk Woll, Dec 08 '10 at 19:12

score 0 · Answer 1 · answered Dec 08 '10 at 20:05

0

DECLARE CURSOR on the Entity.

OPEN CURSOR.

For each FETCH CURSOR

    SELECT the child rows for the current Entity.

    Write the output.

CLOSE CURSOR.

answered Dec 08 '10 at 20:05

Gilbert Le Blanc

50,182
6
67
111

score 0 · Accepted Answer · answered Dec 09 '10 at 15:49

So what I ended up doing was having a nested DataReader, one outer one for the parent entity to get all of the parents, and then one inner one that reads all of the children in one statement using the reader.NextResult() method similar to this:

var exampleSql = "select * from child1Table; " +
                 "select * from child2Table; " +
                 "select * from child3Table"; 
                 // and so on for the other child tables
using (var outerReader = cmd.ExecuteReader())
{
    while (outerReader.Read())
    {
        var entity = new Entity();
        entity.Prop1 = outerReader[0];
        entity.Prop2 = outerReader[1];
        //.... etc.

        using (var cmdInner = new SqlCommand(exampleSql))
        using (var innerReader = cmdInner.ExecuteReader())
        {
            while (innerReader.Read())
            {
                var child = new Child1();
                child.Prop1 = innerReader[0];
                // ... etc.
                entity.Children1.Add(child);
            }
            innerReader.NextResult();
            while (innerReader.Read())
            {
                var child = new Child2();
                child.Prop1 = innerReader[0];
                // ... etc.
                entity.Children2.Add(child);
            }
            innerReader.NextResult();
            // and so on for the other child entities
        }
    }
}

At least this way, I'm only sending one sql statement to the database to retrieve all of my child entities per parent instead of a separate statement per child per parent.

If anyone has a better way, please feel free to let me know.

Btw, my example code is just pseudo code. The real thing is using parameterized queries and no select stars, just the columns i need. The intent is to show the approach, not the actual implementation.

What is the most effecient way to pull thousands of records with hundreds of thousands of child records into class objects?

2 Answers2