CFQuery CachedAfter attribute


#1

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.


#2

Copying this here from the JIRA ticket in response to @micstriit’s concern that the default Adobe behavior was dangerous since it could fill up memory on Lucee:

The reason the Adobe behavior is not dangerous on Adobe is because there is an admin setting called “Number of cached queries” which puts a max on the number of queries that will be cached and applies a FIFO eviction policy to clear old ones.

Lucee does not have this setting so far as I know. Perhaps the real fix is for Lucee to also implement the same max query cache that Adobe has? Or at least put in some sort of cap so the query cache won’t grow unbounded. Then we can support the same query caching as Adobe without it being dangerous.


#3

@bdw429s Actually with Lucee you can have the same limitation if you like.
Simply use an EHCache for cfquery and then limit the amount of elements hold in memory.
That’s it! But sure most people will not do that, maybe we should make a video about it :wink:

But it is interesting that this setting exist in ACF in the first place, there is for sure a reason for it, i’m sure it did not exist in the past!
Most likely my assumption that people did run in troubles with that, was not that far off :wink:

@cfvonner As i pointed out before, the attribute was not ignored by Lucee it simply was readonly. Means it has not written to the cache but read from it.
To be honest the type of the ticket “incompatibility” is not my biggest concern, my biggest concern is to have a solution that is neutral or beneficial for most users.
That ACF does not support to have both attributes is a good input i missed so far and it needs to be addressed.

I think the best solution is to implement
https://luceeserver.atlassian.net/browse/LDEV-2084
that way you can get the same behaviour as ACF , by simply set a timespan in the admin or the application.cfc/cfapplication, only question what will the default value be.
We also can add the possibility to limit the amount of elements even certain caches already allow this.