Reputation: 711
I have a pretty simple one to many mapping using Fluent NHibernate and it works fine except the the queries NH generates are less than ideal. I found this when I checked the sql log from NH.
The query I am looking for is like
select p.*, c.* from parent p inner join child c on p.Id = c.parentId
where p.ID is the PK of the parent table and c.ParentId is a FK in the child table pointing to the PK of the parent table.
But what I found from the sql log is something like this:
select P.* from Parent
followed by a bunch of queries running against the child table like
select * from child c where c.ParentId = @p0
Not surprisingly this is causing performance issues.
My question is why NH is not generate the inner join query I think it should? What do I need to change so that NH will generate the desired query?
Upvotes: 1
Views: 639
Reputation: 123901
The JOIN is not suitable in this scenario. It will produce SELECT resulting in more rows, then the Parent
table has. I.e: each child of a parent will append new row.
The appropriate solution here is to keep these selects separated. Firstly select the parent, then load all children for all parents (just selected parents). NHibernate does have nice solution for this:
This would work a bit similar as you experienced. Firstly select parent, then go for child(ren). The biggest difference is, that instead of 1 + N
(going for each children collection separately) we now go for more children in batches. so it could be 1 + 2
Examples of the batch size mapping
1) Colleciton
HasMany<Child>(x => x.Children)
.BatchSize(25);
2) Entity itself
public ChildMap()
{
Id(x => x....
...
BatchSize(25);
There are many other advantages, where the PAGING is one of the most profitable. Because we are working with a flat (not multiplied) Parent, we can apply .Take()
and .Skip()
Finally, fi we would like to find some Parents based on their children, we can use in this scenario Subqueries: 15.8. Detached queries and subqueries
Upvotes: 1