NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

Question

I'm trying to select an entity and fetch a related list:

    Session.QueryOver<UserRole>()
           .Fetch(x => x.UsersInRole).Eager
           .List();

Which results in a lot of database hits. The first one is something like:

 SELECT ... FROM UserRoles
 left outer join UsersInRoles on ...

And hundreds more seperate queries which looks something like:

 SELECT ... FROM UsersInRoles
 left outer join UserRoles on ...
 WHERE UserRoles.UserId=?

The mapping is as following:

public class UserRoleMap : ClassMap<UserRole>
{
    public UserRoleMap()
    {
        Id(x => x.Id);
        Map(x => x.RoleName);
        HasManyToMany(x => x.UsersInRole)
        .Inverse()
        .LazyLoad()
        .Table("UsersInRoles");
    }
}

Romain

Answer 1

I would say, that this behaviour is what we should expect. Let's have a scenario, in which we have in the system 2 users and 2 roles

User1 - Role1 // has only Role1
User2 - Role1 // now we see that Role2 has more then User1
User2 - Role2

Let's say, that the first query, will retrieve only User1 and its many-to-many relation Role1. What we have in the ISession at the moment is only User1, so the set of Users for Role1 is incomplete (we cannot reuse objects loaded into ISession at the moment). But how should one know where we are? That all data loaded for Role1 is or is not in the session?

New query, loading the data for Role1 must be issued. And this way, we can at the end have dosens of these queries...

What I see as the best solution (I am using it in almost all scenarios) is the batch-size setting: 19.1.5. Using batch fetching

HasManyToMany(x => x.UsersInRole)
  ...
  .BatchSize(25)

Mark all your collection maps with .BatchSize(25) and do that even for the Class map as well. That will cause more then 1 SQL Script, but at the end not more then 1 + (2-4) dependent on the batch-size and page size.

Bertina

Tried your suggestion. Resulted in 1 + 12 queries - MUCH better then before, but isn't there's a way to make it less then 12? (increasing the batch size?) - what is the cost of batch size? I would geuss that if it was all good it would have been set by default. Is the batch size the only solution? Isn't there's a possible join that will results in 2 queries? If I would of needed to write it my self with simple SQL I think 2 queries would have been enough. Thanks. – Romain

If there is any better solution than batch-size, maybe, I am not sure. The reason why this is not turned on by default, could be the fact, that this is breaking straightforward logic: The Proxy of uninitialize object is a driver. It directly goes for its data when needed. The batch-sizing moves some more effort on session to join these SELECTS. Anyway, Batch size is on all our collections and class mappings. And the performance is amazing even on large sets – Woke