A Primer on ColdFusion’s Built-in Query Caching

As I mentioned in the introduction, ColdFusion provides a built-in query caching method. This method uses two simple settings—one that specifies the length of time to cache a query in a tag attribute, and the number of queries that can exist in the cache in the ColdFusion Administrator.

Using the Cfquery Tag's cachedWithin and cachedAfter Attributes

The CFQUERY tag has two optional attributes for caching queries: cachedWithin and cachedAfter. In my experience, developers use the cachedWithin attribute more often than the cachedAfter parameter; the difference between them is minimal.

The cachedWithin attribute represents a period of time for caching a query; you specify the value with the createTimeSpan() function. The first time ColdFusion executes the query with this attribute, it caches and tags the query with the current date/time stamp. After that, each call to the CFQUERY tag evaluates the current date/time and specified time span against the cached query’s date/time stamp. If ColdFusion compares the dates and determines that the cached query has not expired, ColdFusion retrieves the previously cached record from memory and makes it available in the executed page. Of course, if the cached query has expired, then ColdFusion executes the query again and caches the new recordset, tagging it with the current date/time stamp and starting the cycle all over again.

The cachedAfter attribute represents a single date/time after which ColdFusion will cache a query indefinitely. Until the specified date/time passes, ColdFusion will continue to execute this query against the database and return a new recordset. However, once the specified date/time passes, ColdFusion will cache the recordset in memory and use the cache for all subsequent CFQUERY calls.

Using the Maximum Number of Cached Queries Setting

You can find this setting in the ColdFusion Administrator in the Caching section. As you may have guessed by its label, you specify a maximum number of queries that ColdFusion can cache into memory. This setting prevents you from clogging ColdFusion’s memory space with an excessive number of cached queries.

There are a number of pros and cons associated with using this method of query caching.

Pros

• This caching method is native to ColdFusion and is relatively simple to implement, requiring very little code changes.
• When you cache queries with this method, you store them in memory, providing the fastest storage and retrieval.

Cons

• This caching method offers no alternative to storing the cached queries in memory. Because disk space, by comparison, is often more abundant and less expensive than memory, this limits your ability to cache queries on a larger scale.
• The ColdFusion Administrator setting, Maximum Number of Cached Queries, is a server-wide setting. This means that if you run multiple web applications under a single instance of ColdFusion Application Server, this setting limits the cached queries from all web applications. If your maximum setting is 100 and your first web application has 100 cached queries, then your remaining web applications have no room to store their cached queries without bumping other queries out of the cache.
• The Maximum Number of Cached Queries value is just that—a number of cached queries. Since you can't predict the recordset sizes that your cached queries can return, it is impossible to estimate the amount of memory that the cached queries might need.
• When using the cachedWithin parameter, there is no easy way to clear the cached query. Clearing a query when you have used this method requires that you execute the query with a time span of zero (for example, createTimeSpan(0,0,0,0)). Additionally, you cannot clear the cached query without executing it.
• When using the cachedAfter attribute, there is no easy way to clear the cached query. Clearing a query when you have used this method requires you to execute the query while passing a date/time that has already passed (such as 8/1/1975). Additionally, you cannot clear the cached query without executing it.
• When you have cached queries using the cachedWithin or cachedAfter attributes, you cannot include cfqueryparam tag calls. Since the cfqueryparam tag offers many useful features for checking and enforcing data types, this is a big drawback. This limitation does not, however, apply to the solution I present on the following pages.

Page 2 of 5

Previous Page

Next Page