NHibernate: Solving N+1 in master-detail where detail has many-to-one relationship?

Question

I have an Order which has a collection of OrderLines. Every OrderLine has a many-to-one to a Product. For all Orders I have a searchwindow which - by default - shows all Orders in a grid. Using this same window I can filter all Orders which contain a given Product. Our customer generates about 700 Orders per day and orders have an average of about 35 lines.

In the searchwindow I only show information stored within the Order object itself. But, if I filter for a specific Product, it will initialize my lazy loaded relationship to the collection of OrderLines as well as the many-to-one to my Product within my OrderLine. Each orderline is queried line by line, and for each line there is a seperate query to the product table.

so on average I get 35 queries per order to initialize all the OrderLines, plus 35 queries to fetch the products associated with that orderline.

What is the best way to load all orderlines and their associated product at once (and if not possible all orderlines per order)? I know I can map my many-to-one to the product in the orderline as a fetch="join", but that will still leave me with the 35 single queries to get the orderlines+products.

FYI: Currently I load everything lazy.

Any suggestions? Can I use Future here?

Regards, Ted

Answer 1

What is your batch-size set to?

Leave your lazy loading as is and on both collection mappings set it to 35, this should reduce 1 + 35 + 35 queries to 1 + 1 + 1.

<bag ... batch-size='35' ..> 

Also as pointed out by Stefan you need to set batch-size='35' on the class as well.

Answer 2

In such cases, I would not retrieve the orders entities itself. I would create a 'View' class, and by using NHibernate Projects, I'd return those 'View' classes which only contain the information that you're interested in, and put that in the overview-form or page. When searching, you can then execute a new query (only one), which retrieves the 'OrderView' instances that you're interested in.

When you'd want to edit an Order, or see its details, then, I would load the entity.

Answer 3

Do you do the filter operation in memory on the loaded entities? Usually you would like to execute a query for only those orders containing an orderline for the product in question. So then you only would need to populate your window based on the query result. There's no need to traverse all orders and all order lines to fill a window with only a small portion of the data.

Filtering on the database server is usually a lot faster than fetching all data from the server and doing the filtering in C#.

Your description is not fully clear to me: given that you have already filtered the orders to be displayed, would you need to access the order lines and products at all?
- If not, you could go with the above solution.
- If yes, you could do a join fetch on the filtered order query to directly load the order lines together with the orders. Join fetching is not only possible for <many-to-one> but also for <one-to-many> so you can load some root entities together with their children at once. There's some drawback if the root entity has many, many columns and more than a few children because the query redundantly retrieves the root entity's data for for every child. In many scenarios however (depending on the overall system configuration) the benefit of join fetching heavily outweighs the cost.

In situations where you query data and it is not clear beforehand which data will be actually needed by the following code, using batch selects (with <batch-size>) can help reduce the number of database roundtrips drastically.