Hi, I would like to know the best way to populate an object that has a collection of child objects and each child object may inturn have a collection of objects, from database without making multiple calls to the database to get child objects for each object. basically in hierarchical format something like for example a customer has orders and each order has order items. is it best to retrieve the data in xml format (SQL server 2005), or retrieve a dataset by joining the related tables together and then map that the data to the object? thanks in advance for your help.
+1
A:
You may take a look at ORMs such as NHibernate and Entity Framework that are designed exactly for such scenarios.
Darin Dimitrov
2010-05-04 17:04:33
The ORMs will still make multiple calls to the database, won't they?
Tiberiu Ana
2010-05-04 17:07:05
@Tiberiu Ana - Can only speak for NHibernate, but you can avoid multiple database calls by using eager fetching or specifying joins in your HQL syntax.
Ben Hoffstein
2010-05-04 17:09:09
I want to see proof of that. Unless its using a recursive query, or it reads every object in the table at once, its going to have to hit the database multiple times. I don't think a lot of the databases NHibernate supports will support recursive query.
Frank Schwieterman
2010-05-04 17:13:00
(yes an ORM will fill in the object graph, and potentially in a single call to the ORM, but the ORM will hit the database multiple times to do this.)
Frank Schwieterman
2010-05-04 17:15:42
+1
A:
There are a lot of variables still there:
- Are the child objects of the same type? If so you can select them all at the same time and then set up the parent/child relationships in your object mapping layer.
- Can the child objects have children of their own? If the nesting is unlimited, then you can't get all the data at the same time unless you get all the data.
You could certainly do a join on all of the customers->orders->order items and break everything up in code, but that seems like that would be a lot of overhead in duplicated parent rows and a lot of work in processing that big mess.
Trying to avoid doing multiple calls might be a pre-mature optimization. Are you having performance problems with doing too many calls to the database?
Edit: Based on your comments, you should be able to do one query per hierarchy level:
Select * from orders
where orders.customerid = my_customer_id
--Do some orm mappings and make a list of child object ids--
Select * from child_order_object
where child_order_object_id in (list of child object ids)
--Do some more ORM mapping and link child objects to previous parent objects--
...
--Repeat for more levels--
You should be able to have just one query per relationship level rather than the exploding amount of queries to get just one object by id.
Alan Jackson
2010-05-04 17:10:25
no, the child objects are not of same type. yes it is causing performance issues when I need to retrieve large amount of data in one go and also I think it is not the efficient way. lets say the customer has 30 orders and assuming each order has another collection of child objects apart from the order items, that would be 60 calls to retrieve all orders for a customer (two database calls for each order), fully populated with child objects. it is just an example used here to explain the problem.
RKP
2010-05-04 17:35:10
we are not using any ORM tool in our project, so conversion of all the existing data access code to use the tool is not an option at the moment. so I think either I need to retrieve a joined dataset and then parse it to construct the hierarchy of objects or get an xml from the database. anyways thanks for the information.
RKP
2010-05-24 13:18:29
A:
MS SQL 2005 supports Common Table Expressions, which can be used for this purpose. Basically they allow you to do a recursive query. Do a keyword search on CTE / MS SQL and you'll find a lot of stuff like this: http://stackoverflow.com/questions/2737692/apply-a-recursive-cte-on-grouped-table-rows-sql-server-2005
Frank Schwieterman
2010-05-04 17:14:39