A while back (May of 2017 to be precise) I entered JIRA Ticket LDEV-1304 to add the cachedAfter
attribute to the <CFQUERY>
tag. This attribute was previously ignored by Lucee.
The way Adobe ColdFusion (ACF) implements the cachedAfter
behavior is as follows. If you specify a future date/time value for the cachedAfter
attribute on a <CFQUERY>
tag, each time the query is run prior to that date/time value it will hit the database and return the result. Once that date/time value is reached, the first time the query is run (taking into consideration any changes in SQL syntax in the query definition or query parameters) it will once again hit the database, but in addition to returning the result, it will also cache the result. Any subsequent calls to that exact query will be served from the cached query, and the database will not be hit ever again. This cached query will persist until the next server reboot or flushing of the query cache.
It should be noted that ACF does not permit the cachedWithin
attribute be used in tandem with cachedAfter
(it throws an “Attribute validation error for tag CFQUERY.” error).
It seems from anecdotal evidence that this is a seldom-used feature. It is potentially dangerous if used without careful consideration of the query statement and it’s impact on the query cache. For example, given a <CFQUERY>
that contains a single <CFQUERYPARAM>
in the WHERE
clause, and the query fires after the cachedAfter
date/time value has passed, it will cache a query for each valid unique value passed in the parameter. That might be only a small number of queries, or it could be thousands of queries, or more.
The way that this is apparently going to be implemented in Lucee will be slightly different - there will be “guard rails” on the caching. Per the comments on that JIRA ticket, one would have to use a combination of cachedAfter
and cachedWithin
to control the lifespan of the cached query. Assuming your cachedAfter
date/time value was 2pm today, and your cachedWithin
was set to be 2 hours, the query would not be cached until it was run the first time after 2pm - and then it would be held in the cache until 4pm. Calls after 4pm would then re-query the database and cache for another 2 hours, etc. If one only used cachedAfter
in the <CFQUERY>
tag, no caching would ever be done (the attribute would be ignored). In order to mimic ACF’s behavior, one would have to set a cachedWithin
attribute with the largest possible value for a CreateTimespan()
function call.
While I see the potential benefit to this enhanced behavior, I disagree with it for the main reason that this is incompatible with ACF’s implementation - and the JIRA ticket was submitted as an “Incompatbility” issue. So the solution to the JIRA ticket should be to make it compatible with ACF by default, full stop. The enhanced behavior should be optional. Then we could put in a corresponding ticket with Adobe to enhance <cfquery>
to allow the combination of cachedAfter
and cachedWithin
in ACF.
I created a repo on GitHub so anyone interested can play with this. It includes CommandBox server.json
files for both ACF and Lucee, and an embedded database so it can be run with almost zero effort.
I’m interested in hearing other’s opinions on this.