Pagination is a pretty normal approach these days to presenting large amounts of data to a user in easy to digest amounts. Instead of presenting a ridiculously long list of data with hundreds, maybe thousands of rows, the obvious choice is to break it into sizable chunks and then go from page to page, sorry if this sounds obvious.

There are many many ways to achieve the result of pagination but probably the most common way in Coldfusion is to execute a query, then using cfloop, present the data using the startrow and endrow attributes and present a maximum amount of rows per view, say 10.  The problem here is that if you have your pagination working so that every time you move from one page of results to the next, it refreshes the page and re-executes the same query, well, if there are a lot of results, that becomes a lot of unnecessary processing and data transfer between the coldfusion server and the database server. Think of it like this, if each time you are running a query and 1 mb of data has to be transferred it would work something (very roughly) like this:

CF template is executed > CF makes a request to the SQL server via the network > SQL server executes the query and returns the data via the network > CF puts the data returned into memory > CF executes the rest of the template to present the data.

If you have a busy site and this same query is being executed over and over, dragging this large chunk of data across a network everytime then expect your site to run very badly. This is where query caching comes into play and makes everything slinky and sexy….

The geniuses at the Coldfusion factory thought about this very issue eons ago and implemented a nice and simple way of caching repeated queries by introducing an attribute for the CFQUERY tag called ‘cachedwithin‘. Basically, all you need to do is create a time span and add it to this attribute like so:

<cfquery name="qReturn" datasource="#application.dsn#" cachedwithin="#CreateTimeSpan(0,0,6,0)#">
BIG NASTY QUERY
</cfquery>

The above would make this query cache in the servers memory for 6 minutes before it went and got a fresh copy. So instead of going through all the time waiting for a SQL server to execute a complicated query and return a giant chunk of data, it’s already there in memory waiting for you to use! how cool (I mean sexy) is that!

There is one more thing to add… what if the query you are caching is set to only update every 24 hours but you need to purge it to update the results to appear in this recordset now! Well luckily there is a bit of a hack to get around this, you see while the people in CF land made it easy to cache a query, there is nothing really obvious to purge that query from cache. The only way to do this is to execute a query of the same name and same SQL syntax and set the time span to -1 second, that’s it, pretty easy.

<!--- reset nav cache --->
<cfquery name="qReturn" datasource="#application.dsn#" cachedwithin="#CreateTimeSpan(0,0,0,-1)#">
BIG NASTY QUERY
</cfquery>

Now the next time your template executes it will grab a fresh set of that data and keep it in cache for whatever timespan you desire,  SEXY!