nhibernate hql subquery performance

Question

I have written an hql to support paging

            string hql = @"select distinct mr 
                       from MediaResource as mr
                        where     mr.Deleted= false
                            and   mr.Type = :typeId";

            SimpleQuery<MediaResource> q = new SimpleQuery<MediaResource>(hql);
            q.SetParameter("typeId", typeId);
            q.SetQueryRange(page * pageSize, pageSize);
            return q.Execute().ToList();

And then I wrote a test to run this function and get the nhibernate log as

 select
    * 
from
    ( select
        distinct mediaresou0_.MediaResourceID as MediaRes1_7_,
    from
        MediaResource mediaresou0_ 
    where
        mediaresou0_.Deleted=0 
        and mediaresou0_.Type=:p0 ) 
where
    rownum <=:p1;
:p0 = 1, :p1 = 10

What concerns me is the select * from (select ...) part. Would this be a performance problem? Is it possible to tell Nhibernate to generate sql statement to have only one query?

Answer 1

The performance loss probably won't be noticeable unless you have a large amount of records. In that case, you'll be pulling back all records, and then taking what you want.

The alternative is to use something like this:

 SimpleQuery<MediaResource> q = new SimpleQuery<MediaResource>(hql);
        q.SetParameter("typeId", typeId);
        q.SetFirstResult(page * pageSize).SetMaxResults(pageSize);
        return q.Execute().ToList();

SetFirstResult will do just as it says - it sets the index at which results are fetched from going forward.

SetMaxResults then works off of this and gets the pageSize amount of rows. (effectively a sql top where id > xx)

Answer 2

As far as I am aware, this would not be a performance issue unless, as ddango mentioned, there were a huge amount of rows. Your query is selecting from a sub-query, not running two separate queries to the database server, which is what some people do (and is horrific for performance). You will only ever be returning the correct result set at the end of the query, I believe for it to work in this way (using rowcount) there needs to be a sub-query run.

My suggestion is leave it as it is, you shouldn't have any issues with speed if the table is correctly indexed for the search as the query really isn't that expensive as it's all done in the database side, it's not like you're actually pulling details for every single object in the sub-query to your application and building them into objects.

As for your other question

Is it possible to tell Nhibernate to generate sql statement to have only one query?

I don't believe that NHibernate can be triggered to implicitly generate more optimal solutions, the only way to change this is to change your method of getting this data, but I don't see any issues with the sub-query myself :)