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
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
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.