Nhibernate Criteria/SQL Query problem

Question

Hi

I have a querry that returns a large number of rows. I want to know if there is a way to decrease query time without changing the results.

One smsblast contains multiple smsresponses and smsresponse contains multiple smsstatuses.

Variables

status = int

smsBlastId = int

var queryStatus = DetachedCriteria.For<SmsStatus>()
 .Add(Restrictions.Eq("Status", status))
 .SetProjection(Projections.Property("SmsResponse"));

var queryStatusPrio = DetachedCriteria.For<SmsStatus>()
    .Add(Restrictions.Le("Status", status))
    .SetProjection(Projections.Property("SmsResponse"));

var q = _session.CreateCriteria<SmsResponse>()
  .Add(Restrictions.Eq("SmsBlast.Id", smsBlastId))
  .Add(Subqueries.PropertyIn("SmsStatus", queryStatus))
  .Add(Subqueries.PropertyIn("SmsStatus", queryStatusPrio))
  .SetFetchMode("SmsStatus", FetchMode.Eager)
  .SetResultTransformer(new DistinctRootEntityResultTransformer())
  .List<SmsResponse>();

This query generates following SQL:

SELECT this_.Id                   as Id13_1_,
   this_.CustomerID           as CustomerID13_1_,
   this_.MobileNumber         as MobileNu3_13_1_,
   this_.MessageId            as MessageId13_1_,
   this_.SmsBlast_id          as SmsBlast5_13_1_,
   smsstatus2_.SmsResponse_id as SmsRespo4_3_,
   smsstatus2_.Id             as Id3_,
   smsstatus2_.Id             as Id9_0_,
   smsstatus2_.Status         as Status9_0_,
   smsstatus2_.TimeRecived    as TimeReci3_9_0_,
   smsstatus2_.SmsResponse_id as SmsRespo4_9_0_
FROM   [SmsResponse] this_
   left outer join [SmsStatus] smsstatus2_
     on this_.Id = smsstatus2_.SmsResponse_id
WHERE  this_.SmsBlast_id = 112560 /* @p0 */
   and this_.Id in (SELECT this_0_.SmsResponse_id as y0_
                    FROM   [SmsStatus] this_0_
                    WHERE  this_0_.Status = 5 /* @p1 */)
   and this_.Id in (SELECT this_0_.SmsResponse_id as y0_
                    FROM   [SmsStatus] this_0_
                    WHERE  this_0_.Status <= 5 /* @p2 */)