Queries cached by cachedwithin. Where?

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()>

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

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

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.