pagination for performance intensive queries using nhibernate and sql server temporary tables

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- first query
SELECT pi.*
INTO #TempPerfIntensiveTable
FROM
  .. a 100 JOINS or SPATIAL FUNCTIONS ...;

SELECT COUNT(*)
FROM 
INTO #TempPerfIntensiveTable;

-- end of first query

-- second query
-- :skip and :take are sql parameters for pagination
SELECT pr.* FROM 
(SELECT tp.*, ROW_NUMBER() OVER(ORDER BY tp.Id) AS ROWNUM
  FROM #TempPerfIntensiveTable tp)pr
WHERE pr.ROWNUM BETWEEN :skip AND :take + :skip;
-- end of second query


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

I am currently working on LiveForm which makes setting up contact forms on your website a breeze.