Programming ColdFusion

Query Caching

If you want to squeeze every last bit of performance out of your ColdFusion applications (and who doesn’t?), you might want to consider query caching. Query caching allows you to retrieve query result sets from memory as opposed to requiring a round trip to the database. This can significantly reduce the amount of time it takes to return a result set in your application. Query caching is implemented using the CFQUERY tag and one of two optional attributes:

CACHEDAFTER

Specifies a date for using cached query data. Cached query data is used only if the date of the original query is after the date specified in CACHEDAFTER.

CACHEDWITHIN

Specifies a time span (using the CreateTimeSpan( ) function) for using cached query data.

Query caching is especially useful in situations where you repeatedly execute the same query to obtain a result set that remains static for a known period of time. Some examples of queries that are candidates for caching include:

• A query that retrieves a “what’s new” list that is updated once a day

• A query that retrieves a company’s closing stock price on a heavily trafficked Intranet site that is updated once a day

• A query that retrieves a list of users to use in an administration application

Regardless of the type of query you want to cache, one guideline is absolute: the CFQUERY statement (including the SQL) that references the cached data must be exactly the same every time. For this reason, queries that use dynamic SQL aren’t candidates ...