NHibernate ignores SetMaxResults in SQL

Recently me and my team realized that several our queries, that include maximum limit on the recordset, are limited in the web application and not on the Oracle side.

First thought was that somebody simply forgot to add an invocation of SetMaxResults method, but our repository code was very obvious about it:

IQuery query = session.CreateQuery(hqlQuery);
query.SetMaxResults(100);

After some investigation it turned out that NHiberante is not able to use ROWNUM on the SQL side, when there is a fetch join on the collection.

This is HQL query we use:

select
    user
from
    User user
    /* next line causes no rownum check */
    inner join fetch user.EmployeeDatas EmployeeData
    left join fetch EmployeeData.Country country

And some NHibernate code:

public IList List(ISessionImplementor session, QueryParameters queryParameters)
{
	// ...
	if ( hasLimit && ContainsCollectionFetches )
	{
		log.Warn( "firstResult/maxResults specified with collection fetch; applying in memory!" );

NHibernate simply ignores the limit when executing the query and applies it on the result.

At some point I thought that it is not possible to even create such SQL query, but I was proven to be wrong. The query would look similar to this proof-of-concept:

SELECT
*
FROM
(
SELECT
*
FROM
TUSER u
WHERE
/* Add conditions here: */
EXISTS(…)
/* Here is DB-side limit: */
AND ROWNUM <= 14 ) AS u /* And now fetch all collections needed */ INNER JOIN TEMPLOYEEDATA d ON u.ID = d.USERID [/sql] So it's clear that it is possible to improve this in NHibernate. In our case change was simple: We changed one-to-many mapping to one-to-one.

But if you are not able to do this because of your requirements I would use custom SQL view and mapped it in NHibernate. The second idea would be to use NHibernate detached criteria and sub-queries.

Finally the unit test that we used to check if the limit is done on SQL database side:

[Test]
public void FindUsersBy_QueryWithLimit_LimitsOnSQLSide()
{
    using (LogChecker logChecker = new LogChecker("NHibernate", Level.Warn))
    {
        IList<user> users = this._context.ExecuteInTransaction(() =>
        {
            UserQuery q = new UserQuery();
            q.UserId = "ILMT";
            q.MaxNumberOfRecords = 14;
            return this._context.UserRepository.FindUsersBy(q);
        });
        Assert.IsEmpty(logChecker.Messages);
        Assert.AreEqual(14, users.Count);
    }
}

Here you can see the implementation of the LogChecker log4net log reader

Tags: NHibernate