Optimized ColdFusion Record Paging and Sorting using SQL Server 2005

While working on a large application with lots of data I began to think about optimizing my record paging.  I began working on an old school approach of using temporary tables and stored procedures to do my record paging when I decided to hit up google to find a better approach.   The simple search of "ColdFusion record paging" didn't bring up anything of use to me but then I searched "SQL Server 2005 record paging" and found something I could use.   In SQL 2005 a new function called row_number() was added which makes record paging much easier and much more efficient.

Using record_count my query looked something like this:

SELECT
*
FROM
(
SELECT 
row_number() over(order by S.name) as rowNum 
,S.siteId 
,S.code 
,S.name 
,S.roleBitMask 
,R.description as regionDescription 
,R.regionId 
FROM 
Site S 
INNER JOIN 
Region R ON R.regionID = S.regionId 
WHERE 
S.deleted = 0 
) AS List
WHERE
rowNum between 1 and 25

This query worked great, however it was leaving out one very important piece of information: the record count. I did not want to make another call to the db to get the record count so a few minutes of research brought me to this solution:

WITH List AS
(
 SELECT 
  row_number() over(order by S.name) as rowNum 
  ,S.siteId 
  ,S.code 
  ,S.name 
  ,S.roleBitMask 
  ,R.description as regionDescription 
  ,R.regionId 
 FROM 
  Site S 
 INNER JOIN 
  Region R ON R.regionID = S.regionId 
 WHERE 
  S.deleted = 0
),
ListRecordCount AS
(
 SELECT
  *
 FROM
   List
  ,(SELECT MAX(rowNum) AS recordCount FROM List) AS recordCount
)
SELECT
 *
FROM
 ListRecordCount
WHERE
 rowNum between 1 and 25

Now I'm only pulling 25 rows from the database at a time, and I'm getting a record count! All that is left is to use ColdFusion to pass in my start and end rows and to pass in different order by clauses as well.
Here is what my final code looks like:

<cfquery name="local.GetSiteListQuery" datasource="#variables.datasource#">
 WITH List AS
 (
  SELECT
    row_number() over(ORDER BY <cfif arguments.SiteListView.GetSortBy() neq "">
           #arguments.SiteListView.GetSortBy()#
          <cfelse>
           S.name
          </cfif> #arguments.SiteListView.GetSortOrder()#) as rowNum
   ,S.siteId
   ,S.code
   ,S.name
   ,S.roleBitMask
   ,R.description as regionDescription
   ,R.regionId
  FROM
   Site S
  INNER JOIN
   Region R ON R.regionID = S.regionId
   <cfif arguments.SiteListView..GetSiteView().GetRegionId() neq 0>
    AND
   R.regionId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetSiteView().GetRegionId()#">
   </cfif>
  WHERE
   S.deleted = 0
  <cfif arguments.SiteListView..GetSiteView().GetCode() neq "">
   AND
    S.code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.SiteListView.GetSiteView().GetCode()#">
  </cfif> 
  <cfif arguments.SiteListView..GetSiteView().GetSiteId() neq 0>
   AND
    S.siteId <> <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.SiteListView..GetSiteView().GetSiteId()#">
  </cfif>
  <cfif arguments.SiteListView.GetStartRow() neq 0>
   AND
    S.siteId > <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow()#">
  </cfif>
 ),
 ListRecordCount AS
 (
  SELECT
   *
  FROM
   List, (SELECT MAX(rowNum) AS recordCount FROM List) AS recordCount
 )
 SELECT 
  * 
 FROM
  ListRecordCount 
 WHERE
  rowNum BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow()#"> 
      AND <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow() + variables.recordsPage#">  
 
</cfquery>

Here's the proof that the optimized paging query works much, much faster  (click on the images for a full sized version that will show you the execution times better):