Nhibernate N+1 query problem

Question

I need help with NHibernate. I'm using 2.1, but have tried this on 3 as well, with same results. Any help most appreciated!

When doing an ICriteria query using NHibernate, it executes both the query, and then for each result in the query, it executes another query to select an associated object, which is already returned in the initial resultset as I am using eager loading. This is of course resulting is dismal performance. Using the mapping file below, the query Nhibernate generates is exactly as expected below :

exec sp_executesql N'SELECT top 20 this_.ContactCode as ContactC1_48_1_, this_.IsActive as IsActive48_1_, contact2_.ContactCode as ContactC1_47_0_, contact2_.ContactFullName as ContactF2_47_0_ FROM Clients this_ left outer join Contacts contact2_ on this_.ContactCode=contact2_.ContactCode WHERE this_.ContactCode like @p0 and this_.IsActive = @p1 ORDER BY this_.ContactCode asc',N'@p0 nvarchar(7),@p1 bit',@p0=N'DAL001%',@p1=1

This query returns a single record, however is followed immediately with the following query, which is retrieving the details for the related contact object, which is already returned in full in the initial query, and of course, when returning many records, N addition queries are executed. This is completely unexpected!

exec sp_executesql N'SELECT contact0_.ContactCode as ContactC1_47_0_, contact0_.ContactFullName as ContactF2_47_0_ FROM Contacts contact0_ WHERE contact0_.ContactCode=@p0',N'@p0 nvarchar(6)',@p0=N'DAL001'

Lazy loading is switched off. The ICriteria code is as follows :

            ICriteria clientsFromContactCodeQuery = session.CreateCriteria<Client>()
                .Add(Restrictions.Like("ContactCode", id + "%"))
                .Add(Restrictions.Eq("IsActive", true))
                .AddOrder(Order.Asc("ContactCode"))
                .SetMaxResults(maxResultCount);

            var clientsFromContactCodeList = clientsFromContactCodeQuery.List();

I have a simple nhibernate mapping file :

  <class name="Contact" table="Contacts" lazy="false">
    <id name="ContactCode">
      <generator class="assigned" />
    </id>

    <property name="ContactFullName" />
  </class>

  <class name="Client" table="Clients" lazy="false">
    <id name="ContactCode">
      <generator class="assigned" />
    </id>

    <property name="IsActive" />

    <one-to-one
            name="Contact"
            class="Contact"
            lazy="false"
            fetch="join"
    />

  </class>

Answer 1

Try setting your max_fetch_depth property to 2 or 3 if you have not. Not sure if this works with one-to-one relationships though.

<property name="max_fetch_depth">3</property>

Answer 2

Turn on lazy loading and then use HQL queries to prefetch required children.

from Client c 
  left join fetch c.Contact 
where 
  c.ContactCode like :id 
and 
  c.IsActive eq true

I don't know the order by syntax off the top of my head and the HQL may need to be tweaked a bit, but that's the core of the solution.

Answer 3

Most bizarrely, after trying various changes to the query, from hql to icriteria formats, nothing worked. However, I noticed some data where the N+1 queries were not being generated. It turns out the primary keys in the contacts and clients table has SPACES at the end, which when removed, fixed the problem!

Very strange, but thanks for the help.