Is there a way to prevent boolean performance problems with NHibernate LINQ provider

Question

We were using NHibernate 2.1 and I upgraded our system to 3.0 to test the new LINQ provider. I compared the linq provider, createquery, and queryover.

Createquery and queryover did pretty much the same thing, same performance. However the LINQ provider did some REALLY funky stuff!

        var items = (from m in NHibernateSession.Current.Query<Listing>()
                     where m.Active == true
                     select m).Take(10).ToList();

        var items2 = NHibernateSession.Current.CreateQuery("from Listing where Active = :val").SetBoolean("val", true).SetMaxResults(10).List();


        var items3 = NHibernateSession.Current.QueryOver<Listing>()
            .Where(m => m.Active == true)
            .Take(10).List();

Sql from createquery & queryover:

select TOP ( 10 /* @p0 */ ) listing0_.PackageID      as PackageID13_,
               listing0_.MatchComplete  as MatchCom2_13_,
               listing0_.ExpirationDate as Expirati3_13_,
               listing0_.Active         as Active13_,
               listing0_.Archived       as Archived13_,
               listing0_.Deleted        as Deleted13_,
               listing0_.UserID         as UserID13_
from   Marketplace.Listings listing0_
where  listing0_.Active = 1 /* @p1 */

Query from LINQ:

select TOP ( 10 /* @p0 */ ) listing0_.PackageID      as PackageID13_,
               listing0_.MatchComplete  as MatchCom2_13_,
               listing0_.ExpirationDate as Expirati3_13_,
               listing0_.Active         as Active13_,
               listing0_.Archived       as Archived13_,
               listing0_.Deleted        as Deleted13_,
               listing0_.UserID         as UserID13_
from   Marketplace.Listings listing0_
where  case 
     when listing0_.Active = 1 then 'true'
     else 'false'
   end = case 
           when 'True' /* @p1 */ = 'true' then 'true'
           else 'false'
         end

The duration from NH Profiler for LINQ is 37/91 compared to 2/2

Is this supposed to be happening? Or am I missing a configuration setting for telling LINQ to convert Boolean comparisons to bit?

Thanks

This is interesting. Do you have perchance a custom IUserType implementation that might be causing this? — kprobst
What happens if you try (m => m.Active) as the predicate instead of (m => m.Active == true)? — mbeckish
@mbeckish No change. I also tried criteria query syntax, and it is exactly the same as queryover. — BradLaney
This is a side effect of comparing a nullable boolean. Doing listing0_.Active = 1 works fine, however doing listing0_.Active <> 1 will not be accurate as null does not not equal 1 if that makes any sense. I'm not sure why only the Linq provider is doing this. — Danielg

BradLaney BradLaney · Accepted Answer · 2011-06-02T16:15:34

Found the solution is in 3.2. This was reported as a bug and fixed for the next version.

Is there a way to prevent boolean performance problems with NHibernate LINQ provider

1 Answers