July 4, 2021
Proposal: cached queries should have “fromCache” and “wasCached” indicators
Comments
(5)
July 4, 2021
Proposal: cached queries should have “fromCache” and “wasCached” indicators
ColdFusion troubleshooter
Legend 123 posts
Followers: 88 people
(5)

I’d like to make a proposal, and I wonder what others think. Let me expand on the title here.

Background

If you’ve used CF’s query caching features, you almost surely know that when you run a query (or stored proc call) that uses cachedwithin or cachedafter, there will be a “cached” indicator in the resulting query metadata (if you dump it), or the debug output (if you’ve enabled it), or the RESULT struct (if you named one for the query).

And that’s always indicated (true or false) whether the result of the query result came from the query cache or not.

But the phrase “cached” is actually misleading, as it can be interpreted (especially by a newcomer to query caching) to indicate “whether the query WAS cached” (its results saved TO the query cache). That’s NOT its current purpose, of course.

Proposal

To prevent confusion, I would propose that new indicators be created (in those 3 places) :

  • “fromCache”: a boolean indicating whether the query results CAME from the query cache
  • “wasCached”: a boolean to indicate whether the query results WERE saved to the query cache

I share more about this in a feature request I just filed in the Adobe Tracker site. I could have left it at that, hoping “someone might see it” but I thought perhaps some in the community may want to hear about this idea directly and perhaps offer feedback (whether here or in the ticket).

In the ticket I offer some sample code, and I acknowledge some other concerns (from the names I propose, to compat issues, and more). I also address those who may think “there’s really no reason to bother tracking also WHETHER the query result was cached” (there are more than one).

As I conclude there, I realize this “cached” indicator has worked like this for over 20 years. We who’ve used CF query caching for years have just accepted that “it is what it is” (an indicator of whether the result CAME from cache) …and perhaps never really cared to know if/when a query result WAS cached. But they are different things, and really they both have their value.

As the saying goes, I wanted to “run it up the flag pole” here.

[And yes, if the idea has merit, perhaps someone could get it added to Lucee before Adobe may. To be clear, Lucee currently returns the same simple “cached” indicator, as yes or no.]


For more blog content from Charlie Arehart, see his posts here as well as his posts at carehart.org. And follow him on Twitter and other social media as carehart.

5 Comments
2021-07-05 12:18:18
2021-07-05 12:18:18

David, thanks.

BKBK, your last comment (about “a long-standing bug”) makes the very point of my post. Now that you see I’m making the same point, and assuming you have read both the blog post and the feature request, do you have a suggestion of how I might reword things to make things more clear? Or does anyone else?

Like
2021-07-05 10:34:26
2021-07-05 10:34:26

In any case, you’ve touched on a long-time bug that has to be fixed.
Explanation of the bug:
Suppose you use cachedWithin to cache a query.
Actual situation: The very first execution of the query returns a value of False for the “cached” property.
Subsequent runs of the query return a value of True for the “cached” property, as expected.
This is a bug because the query was actually cached the first time.

Let me borrow your code to demonstrate this.

<cfscript>
q5 = queryNew(“foobar5”, “”, [[“foo”]]);

cq5 = queryExecute(“SELECT foobar5 FROM q5″, [], {dbtype=”query”, cachedWithin=createTimeSpan(0,0,0,10), result=”cq5res”});
writedump(cq5);
writeoutput(cq5res.cached); //writing out the cached key from the result struct

writeoutput( “<br><br>”);

q7 = queryNew(“foobar7”, “”, [[“foo”]]);

cq7 = queryExecute(“SELECT foobar7 FROM q7″, [], {dbtype=”query”, cachedWithin=createTimeSpan(0,0,0,10), result=”cq7res”});
writedump(cq7);
writeoutput(cq7res.cached); //writing out the cached key from the result struct
</cfscript>

<p>
<cfset cacheIds=CacheGetAllIds(“QUERY”)>

<cfif arrayLen(cacheIds) gt 0>
<cfoutput>
<strong>SQLs of cached queries:</strong> <br>
<cfloop from=”1″ to=”#arrayLen(cacheIds)#” index=”i”>
#i#: #cacheIds[i].getSql()#<br>
</cfloop>
</cfoutput>
</cfif>

Like
2021-07-05 09:25:05
2021-07-05 09:25:05

On a separate, but related note, what I usually look at first to verify whether or not a query has been cached is the query’s execution time. A time of 0 implies cached.

Like
2021-07-05 09:14:12
2021-07-05 09:14:12

From your expanation it appears that FromCache and WasCached are one and the same thing as the current Cached.

Perhaps, I don’t get it. Hence these 2 questions:

  1. Doesn’t the current boolean, cached,  actually mean “the query results CAME from the query cache”?
  2. Doesn’t “the query results CAME from the query cache” actually imply “the query results WERE saved to the query cache”?
Like
2021-07-05 00:29:34
2021-07-05 00:29:34

Brilliant idea, Charlie!

Like
Add Comment