Earlier this month I demonstrated how to do database side pagination using stored procedures. If you have not already read that then I suggest you do so before reading this entry.

Using ColdFusion For Paging

Every example of implementing paging with cfgrid I have ever seen involves using the queryConvertForGrid() function. While it is nice that CF makes data paging so easy to implement I have to take issue with it in terms of efficiency.

When you stop and look at what ColdFusion is actually doing to setup your paged data you realize that it is very inefficient. For example take the following function used by a cfgrid to page through a list of users.

<cffunction name="getUsersForCFGrid" access="remote" output="false" returntype="struct">
      <cfargument name="page" type="numeric" required="true">
      <cfargument name="pagesize" type="numeric" required="true">
      <cfargument name="sortCol" type="string" required="true" default="">
      <cfargument name="sortDir" type="string" required="true" default="">

      <!--- Get all users in order --->
      <cfquery name="getUsers">
            SELECT userid,username 
            FROM users
            <!--- optional filtering here --->
            ORDER BY #sortCol# #sortDir#
      </cfquery>

      <!--- Return only the rows we need for the current grid page number --->
      <cfreturn queryConvertForGrid(getUsers, page, pageSize)/>
</cffunction>

This looks pretty standard in terms of cfgrid paging but lets break down what just happened.

1. Get ALL records in order from the database (where ever it is located).
2. Pass the resultset to queryConvertForGrid() along with the page number and page size to:
   1. Get the total number of records returned in the resultset.
   2. Calculate what the range of records we actually need to display is for the current page.
   3. Extract the specified subset of records from the total recordset.
   4. Return the desired records along with the total number to the cfgrid

Can you see where there might be room for improvement in this process, I know I can.

A Better Way Of Doing CFGrid Paging

In order to improve the efficiency of the above process all we need to do is switch around the order of execution a little.

1. Calculate what the range of records we actually need to display is for the current page.
2. Get a count of the total number of records in the database matching any desired filter criteria.
3. Get just the desired records from the database based on the specified ordering (and filtering).
4. Return the records plus the total to the CFGrid.

In may seem like a small change but it will have a significant impact. So lets get started...