10 Replies Latest reply on Oct 9, 2010 5:08 AM by Adam Cameron.

    Queries cached by cachedwithin.  Where?

    Adam Cameron. Level 5

      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

        • 1. Re: Queries cached by cachedwithin.  Where?
          cfjedimaster Level 3

          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.

          1 person found this helpful
          • 2. Re: Queries cached by cachedwithin.  Where?
            Adam Cameron. Level 5

            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

            • 3. Re: Queries cached by cachedwithin.  Where?
              cfjedimaster Level 3

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

              does give you deep hooks into it.

              • 4. Re: Queries cached by cachedwithin.  Where?
                Adam Cameron. Level 5

                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

                • 5. Re: Queries cached by cachedwithin.  Where?
                  cfjedimaster Level 3

                  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.

                  • 6. Re: Queries cached by cachedwithin.  Where?
                    MediaStorm

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

                    1 person found this helpful
                    • 7. Re: Queries cached by cachedwithin.  Where?
                      Adam Cameron. Level 5

                      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()[i].getKey()">
                      <cfdump var="#oStats#" label="oDataSourceService.getCachedQueries()[i].getStats()">
                      <cfdump var="#oKey.getParamList()#" label="oDataSourceService.getCachedQueries()[n].getKey[m].getParamList()">
                      <cfdump var="#aParams#" label="oDataSourceService.getCachedQueries()[i].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

                      • 8. Re: Queries cached by cachedwithin.  Where?
                        Adam Cameron. Level 5

                        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

                        • 9. Re: Queries cached by cachedwithin.  Where?
                          BKBK Adobe Community Professional & MVP

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