Andrew, I've read all the replies so far, but here are some points which no
one has mentioned that I think may help. (Not trying to be a tall poppy:
just sharing the kind of stuff I share often when I'm helping people
troubleshoot such problems via my services.)
1) First, it is possible that the cache is being flushed--without respect to
the particular query you're focused on, or how it's created. Note that the
query cache is server-wide (one for all, and all in one). Here are a few
ways that could be cleared:
a) Of course, the cache is cleared when CF restarts (don't ignore
that as a real possibility). You mention using the CF Server Monitor. It
reports on its overview page the time/date of the server having started. For
those not running Enterprise or the Developer edition, I show some code to
programmatically determine CF uptime here:
http://www.carehart.org/blog/client/index.cfm/2006/8/13/cfserver_uptime
b) And it could be someone clearing the query cache programmatically
(somewhere else in that or any application, server-wide), which could be
done with the horribly named cfobjectcache (all it does is clear the query
cache, so should have been named cfquerycache) or some undocumented java
object calls (perhaps also the Admin API: I can't recall)
c) Finally, it could also be someone changing the query cache size
in the CF Admin: yep, that clears the cache
Of course, as you noted (and don't think is happening), it could also be
that the individual query could be removed from the cache due to its
filling, and you say you don't think that's happening. That leads to the
next point.
2) Second, you say you are confirming that with the CF Server Monitor:
"looking at the monitor it seems to fluctuate between about 2 and 8 cached
queries in total, so I am pretty sure we aren't hitting the limit."
So let's clarify: which page are you watching? The Query Cache Status page?
Or Cached Queries? To be clear, the former is a great report in that it does
NOT require any of the start buttons to be enabled, so it's essentially
"free". The latter not only requires start monitoring *and profiling* but
here's a gotcha: those must be enabled BEFORE a query is placed into the
cache, otherwise it will not show up in its report. (Again, that's not true
of the Query Cache Status page, so that's your best first bet for saying
"how many queries are in the cache".)
Back to the points above about how the query cache might be being cleared,
you can observe in that query cache status graph if/when any of the above
cache-clear operations may be happening (except a restart, of course, as
that takes out the server monitor, too, but at least you can see that via
the timestamp on the front page.)
3) Finally, as for your wondering about how the cached results are keyed, it
is as you saw: the datasource, queryname, username/password, and sql
statement all form the "key" for the cached entry. If they change, then a
NEW cache entry is created. And that means even if a single space or
line-end character changes in the SQL. (Also, note that I say a new cache
entry is created: the old one is not "overwritten", as some suggested. An
old cache entry is only "replaced" by its removal from the cache: by timing
out, or any of the above.)
And to drive again the point of it being server-wide (within a given CF
instance), note that there's NO reference to the application name nor
certainly the file name or its location. A given cached query could
therefore be (and reasonably should be) shared across all apps on a server,
as long as the key values are identical (and the query is set to be cached
with the cachedwithin or cachedafter attributes). But there's nothing about
that which should be contributing to the confusion you're seeing.
Hope that helps.
/charlie arehart
char...@carehart.org
Providing fast, remote, on-demand troubleshooting services for CF (and
CFBuilder)
More at http://www.carehart.org/consulting
> -----Original Message-----
> From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On
> Behalf Of Andrew
> Sent: Tuesday, June 21, 2011 3:02 AM
> To: cfaussie
> Subject: [cfaussie] cfquery cachedwithin
>
> Hi,
>
> A colleague has asked me to investigate some code that he claims used
> to cache, but at some point recently doesn't anymore.
>
> Sadly, I can't replicate the exact code / query here (I expect my boss
> would have a fit if I did), but to "paraphrase" it, it's something
> like:
>
> <cfquery name="qCount" datasource="somedatasource"
> cachedwithin="#CreateTimeSpan(0,5,0,0)#">
> ....
> </cfquery>
>
> Please take my word for it that the query does not change, but it DOES
> have a couple of nested selects (not sure that would be a factor?).
>
> I set up a test page, and it appears to remain cached for a couple of
> minutes and then disappear from the cache.
>
> The CF Administrator is set to cache 100 queries, and looking at the
> monitor it seems to flucuate between about 2 and 8 cached queries in
> total, so I am pretty sure we aren't hitting the limit.
>
> So my question is - how can I debug this? I believe the requirements
> specified in the doco are being meet, eg:
>
> "the current query must use the same SQL statement, data source, query
> name, user name, and password"
>
> However, one possible thing I can think of is, what if there was the
> same SQL statement being called with a different datasource. Would
> that cause this one to be lost? Or would they both be cached
> independently? Because this is a possibility, as we have a staging
> instance on the same coldfusion instance.
>
> Hopefully that all makes sense...it's a bit hard to explain...
>
> Andrew.
>
> --
> You received this message because you are subscribed to the Google
> Groups "cfaussie" group.
> To post to this group, send email to cfaussie@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
