Tom Mollerus' Weblog

What I tend to do is to cache all entities in the database that the application is going to call frequently, especially if they're often displayed as a group (think of a list of user types, a list of navigation items, or a list of product categories, for example). Let's start with the following query:

<!--- Get a list of all categories, and cache it for one day --->
<cfquery name="qCategory" datasource="#REQUEST.contentDSN#" cachedwithin="#CreateTimespan(1, 0, 0, 0)#">
	SELECT categories.categoryID, categories.categoryName
	FROM categories
	ORDER BY categories.categoryName ASC
</cfquery>

Note that queries are uniquely cached by name and by the SQL code they contain, including whitespace (whitespace means spaces, linebreaks, and tabs). If the SQL code changes, even by whitespace only, the query is sent off to the database and then recached. So to make sure that the whitespace in the query doesn't change, I don't embed the query in different pages-- I put it in a custom tag (let's call it qCategories.cfm) and call it as needed. (Why I use a custom tag instead of an include will become clear in a little while.)

<!--- Initialize parameters --->
<cfparam name="ATTRIBUTES.refresh" default="0">

<!--- Set the cache time to a week or to nothing as needed --->
<CFSET VARIABLES.cachedWithin = IIF(Val(ATTRIBUTES.refresh), 'CreateTimespan(0,0,0,0)', 'CreateTimespan(7,0,0,0)')<

<!--- Get a list of all categories, cached or refreshed as needed --->
<cfquery name="qCategory" datasource="#REQUEST.contentDSN#" cachedwithin="#VARIABLES.cachedWithin#">
	SELECT categories.categoryID, categories.categoryName
	FROM categories
	ORDER BY categories.categoryName ASC
</cfquery>

Note that the custom tag has one attribute called "refresh". If refresh is set to the default value of zero, then the query is cached for a week. If you pass a positive refresh value to the tag, the query is called with a cache duration of 0, which means that it will be sent to the database and recached. So, we now have a means of controlling whether our query is cached. How do we use it?

When you're calling the query to be displayed, most likely on the front end of your site, you can simply call the custom tag, and then refer to the query:

<cf_qCategories>

<cfoutput query="qCategories">#categoryName#
</cfoutput>

But when you're in the backend of the site, where the editors modify the content of the site, you pass the refresh attribute to the custom tag whenever they make a change to the categories:

<!--- Create/update/delete a category --->
<cfquery datasource="#REQUEST.contentDSN#">
     [Whatever SQL code you need to create, update, or delete a category]
</cfquery>

<cf_qCategories refresh="1">

And there's the solution. Using this technique of embedding your queries in a custom tag to give control over caching means that whenever your editors make updates to the site, they'll be gratefied to see their changes show up immediately. But you'll be able keep the benefits of long term query caching, decreasing the load on your database and making your application faster and more responsive. (Note that to use this technique, the you must be using the same application for both your front and back ends.)