I have a object structure like this: Box has many Item has many Ingredient. When I call a certain method all collections must be filled to perform the task. I thought that it would be better to load collections eagerly in this case, but it is minimal faster in my tests to load them lazyly. A Box has about 200 Item which in turn have about 400 Ingredient.
Why is eager loading with NHibernate (3.3) slower when all collections must be populated?
There are several variables that can affect performance by altering how the different fetch modes work. If you are join fetching everything in one go, consider that the return numbers of rows will be nBoxes * nItemsPerBox * nIngredientsPerItem.
Do you mean that a single item have 400 ingredients? If so, for a single box that would be 1 * 200 * 400 = 80000 rows. In this, the box information would be repeated 80000 times and each item would be repeated 400 times. A lot of data to transfer.
If you turn on SQL logging in NHibernate, what are the actual queries involved?
Are you actually certain, that for each box the items property is accessed and in turn for each item, the ingredient property is accessed?
Otherwise you don't retrieve the same amount of data with lazy loading.
Because it has to load all of the rows from a table and a sub table. Unfortunately Hibernate doesn't always do this in the smartest way. You may use custom queries to load the data more efficiently. But you have to ask the question, do you really want all those rows?
