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, May 26 '11 at 20:29
What happens if you try (m => m.Active) as the predicate instead of (m => m.Active == true)? — mbeckish, May 26 '11 at 20:33
@mbeckish No change. I also tried criteria query syntax, and it is exactly the same as queryover. — BradLaney, May 26 '11 at 20:41
http://stackoverflow.com/questions/6137888/nhibernate-unable-to-cast-boolean-to-string — Vadim, May 26 '11 at 20:42
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, May 26 '11 at 20:42
@kprobst I have an EnumConvention. That is all. The Listing class inherits Entity from SharpArch which defines Id, Equals(), GetHashCode(), GetTypeSpecificSignatureProperties() and IsTransient() — BradLaney, May 26 '11 at 20:44
@Vadim So what you are telling me is I cannot do it. There is no such thing as making a method that compares bit field in DB to bool argument? Also doing this forces it to not use a property. It will make a execution plan with one param, which is the top. — BradLaney, May 26 '11 at 20:52
Found the solution is in 3.2. For now I'm just FUBAR. So I am going to do QueryOver and stop recommending LING-to-NHibernate to the devs I know until this is resolved. — BradLaney, May 26 '11 at 20:58

score 1 · Accepted Answer · answered Jun 02 '11 at 16:15

1

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

answered Jun 02 '11 at 16:15

BradLaney

2,384
1
19
28

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

1 Answers1