Using Database Side Paging With CFGrid

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.

SELECT userid,username FROM users ORDER BY #sortCol# #sortDir#

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...

Step 1: Calculate StartRow and EndRow

This is a simple calculation but something we will want to reuse so I wrapped it up in its own function. The math is pretty self explanatory.

Note: This calculation is for SQLServer where we will start row numbering at one. If you use MySQL you would start numbering at zero.

Step 2 & 3: Get The Data From The Database Plus Total Row Count

Now that we know the startrow and endrow we can query the database for the records based on the ordering, any optional filtering and return just the desired range.

For simplicities sake I am going to swap in the stored procedure from my previous blog entry on Stored Proc Paging. This stored proc returns both the desired records plus the total number of matching records, but you could just as easily use two cfquery's to accomplish the same goal.

Step 4: Format The Return Data For CFGrid

The last operation we need to perform is presenting the data to the cfgrid in a structure it can recognize. I use the term structure intentionally because that is exactly what it expects.

The queryConvertForGrid() function returns a struct with two keys TotalRowCount and query. Its pretty obvious that TotalRowCount key is just the total number of records that exist in the database for the filter criteria and the query is the subset of those records needed for the current page number.

So all we need to do is replicate this structure.

Putting It All Together

Now that we have all the pieces lets put them together.

So there you have it, a more efficient way of doing pagination with ColdFusion and databases. I know it may seem like a lot of work to do considering how easy ColdFusion makes paging, but really all you have to do is write the paging sql as I'm giving you the code for the helper methods here.