ColdFusion Cached Query Manager

(Migrating posts from the old website…)

This is a CFC version of the (older) popular <cfx_queryCache> custom tag. It uses Java reflection to manipulate the underlying ColdFusion classes to gain access to the cached query store. I’ve tested it on ColdFusion MX 6.1 and 7, running on Windows. It is also a little easier to follow than the custom tag version.

This component lets you:

• Get a query in which each row contains the age, hits, and key for each cached query entry currently stored on the ColdFusion server.
• Delete a single query cache entry using the entry’s key.

This is an improvement over the current techniques for flushing any cached queries i.e. running exactly the same query with a timespan of 0 (if you have dynamic queries, this is problematic), or running <cfobjectcache action=”clear”> (which flushes all cached queries).

Usage:
<cfset cqm = createObject("component", "CachedQueryManager").init() />


<!— Returns query with the following fields: hits, rank, penalty (no idea what this means!), creation_time, key.
AGE: The most recently accessed/created cached query is youngest. Records are returned ordered by this field.
HITS: The number of times this query was retrieved from cache.
KEY: The key used to access this query. Cached query keys are constructed by concatenating datasource + sql + name + username + password (username and password would both be ‘null’ if you’re using a datasource set up in Coldfusion Administrator)
—>
<cfset cachedQueries = cqm.getAll() />


<!— Removes cached query of specified key - get the keys by using getAll() —>
<cfset cqm.remove(”mydatasourcenameselect * from bigoldtablequerynamenullnull”) />


<!— Flushes all cached queries by calling cfobjectcache —>
<cfset cqm.clear() />

Disclaimer:

• The tag obtains a lock on the cached query store as it builds the recordset returned when action=”list”. During this period, ColdFusion will not be able to add/retrieve/remove cached queries to/from the store. You can time how long it takes by having a lone call in a test script (hint: it’s very fast).
• Tested CFMX 6.0, 6.1 and 7, running on Windows XP and 2000.

Released under a truncated Apache-style license.

View the source.

Download: cachedquerymanager.zip

(Here’s the old custom tag version.)