Queries cached by cachedwithin. Where?

[Adam Cameron.]

G'day

Anyone know how to use CF's inner workings to probe what's in the query cache?  It seems like it's a bit of a black hole to me.

We are seeing some... err... "idiosyncratic" behaviour with our cached queries, and we want to have a look at how they're stored.

I googled around a bit, but only found a couple of people asking the same question, and no-one answering it...

I'll have a shufti around in the mean time, but if anyone can short-circuit my fossicking around, it'd be much appreciated.

Cheers.

--

Adam

[cfjedimaster]

All I know is that you have no ability to introspect that cache. You

can clear the entire thing - but that's pretty much it.

You know I lie - the Admin API may provide some insight into it - but

I'm pretty sure it won't tell you things like which queries are

stored.

[Adam Cameron.]

Oh, I know there's no sanctioned way of sniffing around - and the Admin API is no help - but CF will be using [something] to put stuff in / get stuff out / check if things are there... I was wondering if anyone's dissected CF enough to know how to do it.

Cheers mate.

--

Adam

[cfjedimaster]

Meh - I'd just skip it and use the new caching stuff in CF9 which

does give you deep hooks into it.

[Adam Cameron.]

Yeah.  Which is all well and good if one is actually using CF9: we're on CF8.

But, yeah, when we go to CF9 (in the pipeline, but it'll not be happening this year), we'll be looking at farming this lot off into ehcache or something.  But that's no help just @ the mo' 😞

Cheers for the follow-up though mate.

--

Adam

[cfjedimaster]

Ah - sorry - assumed you were on latest/greatest. Of course, you can

roll your own caching of course. I never used query caching much

because I preferred to have much more control.

[MediaStorm]

Take a look at  CfTracker over at RIA Forge. It should show you the cache info you are trying to dig up.

[Adam Cameron.]

That's just a blank screen, mate 😉

But yeah, I tracked it down (http://www.cftracker.net/) and it's taking the same approach as I did.  Cool.

Cheers  for the response: wish I'd seen it before I went and covered the same  ground meself.  Not to worry, it was good to stretch my wee brain.

--

Adam

[Adam Cameron.]

I had a shufti around in the CF code and found it.

Here's some sample code, for anyone who's interested:

<cfset oDataSourceService = createObject("java", "coldfusion.server.ServiceFactory").DataSourceService>
<cfset aCachedQueries = oDataSourceService.getCachedQueries()>

<cfoutput>
<cfloop index="iQuery" from="1" to="#arrayLen(aCachedQueries)#">
    <cfset oCachedQuery = aCachedQueries[iQuery]>
    <cfset oKey = oCachedQuery.getKey()>
    oKey.getDsname(): #oKey.getDsname()#<br/>
    oKey.getName(): #oKey.getName()#<br/>

    <cfset oParamList = oKey.getParamList()>
    <cfset aParams = oParamList.getAllParameters()>
    <cfloop index="iParam" from="1" to="#arrayLen(aParams)#">
        <cfset oParam = aParams[iParam]>
        [oKey] oParam.getObject(): #oParam.getObject()#<br/>
        [oKey] oParam.getScale(): #oParam.getScale()#<br/>
        [oKey] oParam.getSqltype(): #oParam.getSqltype()#<br/>
        [oKey] oParam.getSqltypeName(): #oParam.getSqltypeName()#<br/>
        [oKey] oParam.getStatement(): #oParam.getStatement()#<br/>
    </cfloop>
    oParamList.getWhere(): #oParamList.getWhere()#<br/>

    oKey.getPassword(): #oKey.getPassword()#<br/>
    oKey.getSql(): #oKey.getSql()#<br/>
    oKey.getUsername(): #oKey.getUsername()#<br/>
    oKey.toString(): #oKey.toString()#<br/>   

    <cfset oStats = oCachedQuery.getStats()>
           
    oStats.getDSN(): #oStats.getDSN()#<br/>
    oStats.getExecutionCount(): #oStats.getExecutionCount()#<br/>
    oStats.getExecutionTime(): #oStats.getExecutionTime()#<br/>
    oStats.getFunctionName(): #oStats.getFunctionName()#<br/>
    oStats.getHitCount(): #oStats.getHitCount()#<br/>
    oStats.getLastTimeExecuted(): #oStats.getLastTimeExecuted()#<br/>
    oStats.getLineNo(): #oStats.getLineNo()#<br/>
    oStats.getName(): #oStats.getName()#<br/>
    oStats.getSize(): #oStats.getSize()#<br/>
    oStats.getSql(): #oStats.getSql()#<br/>
    oStats.getStartedAt(): #oStats.getStartedAt()#<br/>
    oStats.getStartedAtMillis(): #oStats.getStartedAtMillis()#<br/>
    oStats.getTemplatePath(): #oStats.getTemplatePath()#<br/>
    oStats.getThreadName(): #oStats.getThreadName()#<br/>
    oStats.isCached(): #oStats.isCached()#<br/>
    oStats.isCompleted(): #oStats.isCompleted()#<br/>
    oStats.isStored(): #oStats.isStored()#<br/>

    oCachedQuery.getCreationTime(): #oCachedQuery.getCreationTime()#<br/>
    oCachedQuery.isInUse(): #oCachedQuery.isInUse()#<br/>

    <cfset aParams = oCachedQuery.getParameterList().getAllParameters()>
    <cfloop index="iParam" from="1" to="#arrayLen(aParams)#">
        <cfset oParam = aParams[iParam]>
        [oCachedQuery] oParam.getObject(): #oParam.getObject()#<br/>
        [oCachedQuery] oParam.getScale(): #oParam.getScale()#<br/>
        [oCachedQuery] oParam.getSqltype(): #oParam.getSqltype()#<br/>
        [oCachedQuery] oParam.getSqltypeName(): #oParam.getSqltypeName()#<br/>
        [oCachedQuery] oParam.getStatement(): #oParam.getStatement()#<br/>
    </cfloop>
    <cfdump var="#oCachedQuery.getResult()#" label="oDataSourceService.getCachedQueries()[#iQuery#].getResult()">
    <hr />
</cfloop>
</cfoutput>
<cfdump var="#oDataSourceService#" label="DataSourceService">
<cfdump var="#aCachedQueries#" label="oDataSourceService.getCachedQueries()">
<cfdump var="#oKey#" label="oDataSourceService.getCachedQueries().getKey()">
<cfdump var="#oStats#" label="oDataSourceService.getCachedQueries().getStats()">
<cfdump var="#oKey.getParamList()#" label="oDataSourceService.getCachedQueries().getKey.getParamList()">
<cfdump var="#aParams#" label="oDataSourceService.getCachedQueries().getAllParameters()">

There's some useful info in there (and certainly the info we were after, which is good).

One thing we discovered about the query cache is that when a query in it "expires" due to it being outwith its cachedwith window, CF does not seem to remove the query from the cache.  It simply doesn't use it next time the same query is run.  If this is thethe case, it is a bit rubbish, as it clutters up the server RAM somewhat.  CF ought to be running a sweeper on this stuff, I think.

Anyway.  That's that.

Cheers.

--

Adam

[BKBK]

We are seeing some... err... "idiosyncratic" behaviour with our cached queries,

The cached queries might simply need a refresh. This line should do it:

<cfset oCachedQuery = aCachedQueries[iQuery].refresh()>