Adding to that NHibernate's Linq implementation for Take and Skip before version 4.0 didn't optimize the generation of sql paging, specially if you are using IQueryable to build the sql statement.
Sql Server 2012 & NHibernate 4.0Sql Server 2012 enhanced the way to do paging query, and NHibernate 4.0 embraced these changes.
Implementing the new changesWithout going into details, if you are using Linq's Take and Skip, then you can optimized the generated Sql Statement by using Sql Server 2012 dialect.
So, all what you have to do is to change the line of dialect in the config file to be like this.
Do you want to know the details?Now, let's dive into the details, in case you want to know what is going on. On Sql Server 2008, the way to do paging was to use Row_Number function with Over Clause. If you use Linq to NHibernate to create your queries, and return IQuaryable, and at the end you use Linq's Take and Skip, then NHibernate will wrap your sql with an external query to do the paging.
So the output sql will be something like this
but with Sql Server 2012, the syntax to do paging is simplified, so now you can use the Offset and Fetch First Clauses.
Select Top(10) .............. From ( Select ......................, Row_Number() Over(Order by ....) as _hibernate_sort_row from ..... where .... ) as query Where query._hibernate_sort_row > 20 Order by query._hibernate_sort_row
And NHibernate 4.0 will uses these to generate the sql statement.
So with NHibernate 4.0, using sql 2012 dialect, the new sql statement will be
Select Top(10) .............. Select ...................... from ..... where .... order by .... OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY