Loading Business Object Hierarchy with One Database Call

Question

Hi,

I would like to know what the best practice for populating a business object hierarchy (parent/child/grandchild) structure is from a single database call.

I can think of a couple ways to accomplish it off the top of my head such as:

left-joining all the relations in my sql statement then use looping and logic to fill the business objects

or

use several select statements and 1 datareader and use its NextResult() method to iterate through each result set and fill the corresponding BO's

Just wondering what the best practice for this is

I am using DAAB and c# for my DAL

Thanks!

Answer 1

DataReader NextResult is the best since the amount of data going over the pipe doesn't grow as quickly as the join approach can.

Answer 2

I used to use multiple returned datasets, but the overhead, and the everchanging API's for it, finally let me to return to just using joins to return it all in one gulp.

I keep an eye on the resultset sizes, but in the context of any app I've run into, it's not been an issue. I've not regretted doing so, overall, but YMMV.

Multiple result sets can get especially squirrelly if the parent-level selection clauses involve child-level selection rules.

This way handles all cases; splitting it up works sometimes, but you will end up needing single-set queries in some cases; and it's nice to have just one pattern - especially if you sometimes are stuck with refactoring from one to the other.

Finally you end up with fewer hits on the database, and transaction management is simpler.

Answer 3

There is no universal recipe. It depends on database schema, database size and number of records your application reads in typical scenario. You have two processes here:

• fetching data from database
• populating business objects

Fetching data from database is several magnitudes slower than creating objects in memory. Best way would be to construct select statements for fastest data access.

Queries can be constructed in three ways:

• one large query that fetches everything in single execution - you'll get most complex SQL, and probably the fastest execution (depends on DB schema)
• master/detail approach - simple queries. lots of traffic to database. This is acceptable only if you fetch small number of records, otherwise it is very slow.
• hybrid: one query for each layer of hierarchy. Consider this approach if previous two methods are to slow. This approach requires more complex logic for populating business objects.

You should decide which solution is acceptable. Some key points to consider:

• which SQL is easier to create and maintain - one large that fetches everything in single read or several smaller.
• when you choose on previous point, you should measure performance and make final decision

Answer 4

Have you considered using an OR Mapper such as NHibernate? Eager loading can do what you ask in one call to the database.

If an OR Mapper is not a choice, then I'd throw my vote behind datareader.NextResultSet.

Answer 5

if all the rows are from the same table (or appear to be), then you can pull the data into a dataset with a unary relationship and ADO.NET will link up the hierarchy for you