R – nhibernate hql subquery performance

castle-activerecordhqlnhibernate

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?

Best Answer

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 :)

Related Topic