1 Reply Latest reply on Nov 13, 2006 8:52 AM by ksmith

    query caching ... in cfmx6,7

    Level 7
      Hello,

      How does it work ? I tried simple query in java and same query in
      coldfusion on postgresql without any load. Java gives me consistent
      results, about 280ms on query and 200ms on processing resultset.
      cf on the other side gave me times about 1500ms, 900ms, 450ms, 200ms
      - query cache was set to 0, no arguments in cfquery related to caching
      was not used, just name, datasource. 200ms is under time needed by
      database to process request, so seems to me that cf simply lies !

      Can anyone help me to understand how cf works in this case ?
      query caching is quite agressive technique that little scares me.


      thanks,
      jan
        • 1. Re: query caching ... in cfmx6,7
          ksmith Level 1
          Hello Jan,
          I coded a simple example:
          <cfquery name="test" datasource="cfdocexamples" result="res" >
          select e1.*,e2.* from employees e1,employees e2
          </cfquery>
          It runs in about 25 ms on average.

          I then add the attribute 'cachedwithin="#CreateTimeSpan(0,1,0,0)#" '. It takes about 40ms on the first run. Each additional call takes 0ms. Here is the test output of the query object itself (not the data):
          struct
          CACHED true
          COLUMNLIST E1.DEPARTMENT,E1.EMAIL,E1.EMP_ID,E1.FIRSTNAME,E1.IM_ID,E1.LASTNAME,E1.LOCATION,E1.PHONE,E 2.DEPARTMENT,E2.EMAIL,E2.EMP_ID,E2.FIRSTNAME,E2.IM_ID,E2.LASTNAME,E2.LOCATION,E2.PHONE
          EXECUTIONTIME 0
          RECORDCOUNT 225
          SQL select e1.*,e2.* from employees e1,employees e2

          The query takes no time to run, since the data is cached. This is great for static information, like the classic STATES query. It is also great for code that will not change too frequently, like the page tree of your site. You can always clear a query by setting its cachedwithin to 0. Then re-cache the query. Or just let the cache expire. Your choice.

          I do not believe that CFMX lied about your resulttimes. 250ms is a reasonable query time. My example was run against msaccess and took ~25ms. You can test for yourself by wrapping your query with a getTickCount before and after the query. Then subtract the first value from the second. It will match what the debugging says, I am sure.
          A great place to start is always the cf documentation ( livedocs). The reference guide gives you brief descriptions of the cfquery tag and its attributes. Searching the docs on cachedwithin yields this page. It has a good description and example of caching.