Pagination is a solved problem. A simple google search shows up 11,200,000 results. Whoa! The basic implementation is simple. To paginate a result set you run two almost identical queries, one which fetches the count of the result set. The other which skips and takes the desired slice from the result set.
This is fine in most of the cases, But, when your query is very performance intensive, you just can’t afford to run the same query twice. I ran into a similar situation recently and was searching for a decent approach to solve this problem, and then, I bumped into the awesome Temporary tables in SQL Server. Once I knew about them the solution became very simple. It still needs execution of two queries but it doesn’t run the performance intesive query twice. See for yourself:
These queries need to be executed by calling
session.CreateSQLQuery(query).SetInt32..... This stuff is not specific to
NHibernate, I just put it out there to help future searchers :)