The ColdFusion Query Cache Hammer
I keep my copies of CFWACK readily available at my desk for those moments when my computer is busy (installing software, ftp large files) ... you know what I mean, right? So recently, during one of those moments, I flipped open Volume 2 to page 247. The heading that caught my eye was "Flushing All Cached Queries."
Now, I have to admit, I've not been very good with my "cache" to begin with. However, there have been times when I've used CachedWithin for a CFQuery here and there. I've also been frustrated at how refreshing the cache can be sometimes after inserting or updating a record.
Just in case you don't know, let's assume you have a query such as:
<cfset REQUEST.dsn="cfartgallery" />
<cfquery name="getArtists" datasource="#REQUEST.dsn#" cachedwithin="#createTimeSpan(0,6,0,0)#">
SELECT firstname, lastname FROM Artists
</cfquery>
As you can see, the query will be cached for a period of six (6) hours. However, if you update the Artists table during those six hours, those updates will not be seen until a) the ColdFusion application server has been restarted, b) you force the existing query cache to refresh by placing the same query just after any insert or update has been performed such as:
<cfquery name="updateArtists" datasource="REQUEST.dsn#">
INSERT INTO Artists (firstname, lastname)
VALUES ('Steve', 'Withington')
</cfquery>
<cfquery name="getArtists" datasource="#REQUEST.dsn#" cachedwithin="#createTimeSpan(0,0,0,-1)#">
SELECT firstname, lastname FROM Artists
</cfquery>
The issue(s) I've run into with this is that both SQL statements must be exactly the same, including tabs, spaces, etc. In addition, the CFQuery's attributes, (name, datasource, etc.), must also be identical. Yeah, good luck buddy. Have fun testing your "Cache Machine." I'm not implying you shouldn't employ this method, I just know how difficult (time-consuming and frustrating) it can be trying to get it to work (and test for!). The examples I've used above are not real world examples. As a developer, you know what your queries can look like and how extensive they can be, especially with a few INNER JOIN's and such. I really should mention that when this method is done right, performance gains can be worth the added effort.
Or there's c) CFObjectCache, or what I like to call the CFQuery Cache Hammer. I call it a hammer, because it pounds each and every cached query on the server (or server instance, I assume, in a multi-instance installation) into submission, then discards them like the trash they are (kidding, of course). There is only one attribute, "Action," which is also required. So, you can pound on all of your cached queries with one mighty hammer by simply placing this itsy, bitsy line just after your insert or update query:
<cfobjectcache action="clear" />
I suppose this is the place where I should invoke that famous line from Spiderman, "With great power ..." but I won't.
Comments
I agree, it would be nice to update the query cache another way, such as being more specific about which query(ies) to flush.
I guess what might make this more difficult is when we're using dynamic variables in our queries and caching the output. If I understand correctly, if you had one query (flagged to be cached), and could serve the results of that query say, 3 different ways (due to dynamic variable options, etc.), then ColdFusion actually caches each version, or all three of them (counting toward the 100 max limit). Not sure how you could reference a particular query unless ...
I've got an idea! What if ... ColdFusion added a new attribute for CFQuery, like CacheName="myCacheName" and then you could reference that with the same attribute when you perform an update or insert? I guess that wouldn't work due to what I was mentioning before about storing the same query with each variation, etc.
Until there's a better way, I guess that's why a previously undocumented function (CFObjectCache) became documented and supported.
<cfquery datasource=data cachedwithin="#createTimeSpan(0,6,0,0)#">
select #application.cachebust# as bust, * from employees where...
</cfquery>
You can then clear this cache and all others like it at any time by simply setting application.cachebust=X (a random number, a timestamp, whatever). Insert a record, increment cachebust, and the cache gets updated.
thanks for the tip ... i'll have to give that a try one of these days!
the <cfobjectcache action="clear" /> is a real hammer, every cached query will be deleted. in a huge application this could really have a inpact on performance.
To avoid this, you can assign for every type of query a own cachebust-id. for example, queries for userqueries will have application.cachebust-user, or dbqueries will have application.cachebust-db.
But i think i'll stick with the <cfobjectcache action="clear" /> method. because i haven't to rewrite all my queries.
thanks for the tutorial.
having the same problem with caching a sitemap, can get updated frequently and was looking for a way to force an update to the sitemap after new records added.
This does the trick nicely.