I am running a HQL query to retrieve a list of users so their name and roles can be displayed on a page. However, because the relationship from the User to a UserRole is one-to-many NHibernate executes an additional query for each user to get their roles. So if my search returns 300 users, it will execute an additional 300 queries to get the roles for each user.

My question is this: Does anyone have any suggestions on what I could do with NHibernate to minimize the number of queries in order to get the information I need?

The only work around I could think of would be to load all users, then load every record from the UserRole table and programmatically match up the user to their corresponding roles. My hesitation in doing this is I would have to make UserRole a mapped table (right now it's using an elements to build the collection) which would require a lot of work to restructure our roles.

Any suggestions or thoughts would be greatly appreciated.

Hibernate version:1.2.0.2001

Mapping documents:

Code:

   <class name="User" table="`User`" proxy="IUser">

      <!-- ID -->
      <id name="Id" type="Int32" unsaved-value="0">
         <generator class="native"/>
      </id>

                  ...

      <!-- ENTITY COLLECTIONS [A-Z] -->
      <bag name="Roles" lazy="true" table="UserRole" cascade="all-delete-orphan" access="nosetter.camelcase">
         <key column="UserId" />
         <element column="RoleCode"
            type="LogisticsPlus.EShipPlus.NHibernatePersistence.Types.RoleType, LogisticsPlus.EShipPlus.NHibernatePersistence" />
      </bag>
      
   </class>

Name and version of the database you are using:MS SQL Server 2000

The generated SQL (show_sql=true):

NHibernate: select user0_.Id as Id9_0_, division1_.Id as Id25_1_, customer2_.Id as Id26_2_, user0_.EmailAddress as EmailAdd2_9_0_, user0_.IsActive as IsActive9_0_, user0_.Name as Name9_0_, user0_.Password as Password9_0_, user0_.Role as Role9_0_, user0_.Division as Division9_0_, division1_.ShipToCode as ShipToCode25_1_, division1_.CustomerNumber as Customer3_25_1_, division1_.IsActive as IsActive25_1_, division1_.Name as Name25_1_, division1_.DefaultFreightClass as DefaultF6_25_1_, division1_.DefaultItemDescription as DefaultI7_25_1_, division1_.DivisionExportStatus as Division8_25_1_, division1_.PurchaseOrderIsRequired as Purchase9_25_1_, division1_.PurchaseOrderRequiresValidation as Purchas10_25_1_, division1_.InvalidShipmentFieldErrorMessage as Invalid11_25_1_, division1_.ImporterKey as Importe12_25_1_, division1_.Customer as Customer25_1_, dbo.CountUsersForDivision(division1_.[Id]) as formula0_1_, dbo.CountCarriersForDivision(division1_.[Id]) as formula1_1_, dbo.AverageMarkupForDivision(division1_.[Id]) as formula2_1_, dbo.AverageAdditionalFloorMinimumForDivision(division1_.[Id]) as formula3_1_, customer2_.Name as Name26_2_, customer2_.IsActive as IsActive26_2_, customer2_.LogoUri as LogoUri26_2_, dbo.CountDivisionsForCustomer(customer2_.[Id]) as formula4_2_, dbo.CountUsersForCustomer(customer2_.[Id]) as formula5_2_ from [User] user0_ left outer join Division division1_ on user0_.Division=division1_.Id left outer join Customer customer2_ on division1_.Customer=customer2_.Id where ((@p0 IS NULL)OR(division1_.Id=@p1))AND((@p2=0)OR(customer2_.Id=@p3))AND((@p4 IS NULL)OR(user0_.Name LIKE '%'+@p5+'%'))AND((@p6=0)OR(user0_.IsActive=1))AND((@p7=0)OR(division1_.IsActive=1))AND((@p8=0)OR(customer2_.IsActive=1)) order by division1_.Name , user0_.IsActive DESC , user0_.Name; @p0 = '', @p1 = '', @p2 = '0', @p3 = '0', @p4 = '', @p5 = '', @p6 = 'True', @p7 = 'True', @p8 = 'True'
NHibernate: SELECT roles0_.UserId as UserId__0_, roles0_.RoleCode as RoleCode0_ FROM UserRole roles0_ WHERE roles0_.UserId=@p0; @p0 = '178'
NHibernate: SELECT roles0_.UserId as UserId__0_, roles0_.RoleCode as RoleCode0_ FROM UserRole roles0_ WHERE roles0_.UserId=@p0; @p0 = '73'
NHibernate: SELECT roles0_.UserId as UserId__0_, roles0_.RoleCode as RoleCode0_ FROM UserRole roles0_ WHERE roles0_.UserId=@p0; @p0 = '72'
...

You could try using JOIN FETCH.

Cheers,

Symon.

Thanks a lot Symon. That's exactly what I needed.

I have the performance enhancement that I needed, but now there is one problem with the result set. If a user has 3 roles, then that user will be duplicated in the result list 3 times. I tried several approaches to fix this. Adding DISTINCT to my SELECT clause didn't seem to have any impact, and neither did using NHibernate's DistinctRootEntityResultTransformer.

I ended up writing my own method to eliminate the duplicate entries from the List that my query returns, but I was wondering if there is a better way for NHibernate to do this for me. Does anyone have any suggestions?

Here is the hql:


and here is the log entry I got back from using the DistinctRootEntityResultTransformer:

15:33:08.440 [2624] DEBUG NHibernate.Transform.DistinctRootEntityResultTransformer - transformed: 388 rows to: 388 distinct results

It says that all the rows were already distinct but when I look inside the actual collection, my users are in fact duplicated.

Thanks for your help.

What you are referring to is the "n+1 select problem".

Rather than using fetch="join", try either (A) using fetch="subselect" or (B) leaving the default select fetch, but using batch-size tag to batch queries.

Option A is better, but you need Beta 2 of NH 1.2.0.

kwalters,

Actually, this is a well discussed issue and is considered a feature. You're actually not getting extra objects as such, just multiple references to the same object in the IList.

The solution is to copy the contents of your IList into an ISet to make the results unique.

Cheers,

Symon.