title: How do I tell if a coldfusion query is caching?
body: <p>I have set caching on a query that is getting run a lot of times.. the query itself is not all that slow, but it get's run many times for each request, so figured caching may help.  I have enabled caching, but doesn't really seem to be making a difference.. how do I tell if my query is being cached or not?</p>
  
  <p>I'm setting caching with : <code>q.setCachedWithin("#createTimespan(0, 1, 0, 0)#");</code></p>
  
  <p>Here is my full query preperation: </p>
  
  <pre><code>            q = New Query();
              q.setSQL("SELECT * FROM guest_booking WHERE room_id = :roomID and check_in &lt;= :iDate and check_out &gt; :iDate and status != 0");
              q.setName("checkAvailability");
              q.setCachedWithin("#createTimespan(0, 1, 0, 0)#");
              q.addParam(name="iDate", value="#createODBCDate(arguments.date)#", cfsqltype="cf_sql_date");
              q.addParam(name="roomID", value="#createODBCDate(arguments.room_id)#", cfsqltype="cf_sql_integer");
              qResult = q.execute().getresult();
  </code></pre>
  
  <p>Debug output is showing:</p>
  
  <pre><code>checkAvailability (Datasource=accom_crm, Time=16ms, Records=1) in C:\ColdFusion9\CustomTags\com\adobe\coldfusion\base.cfc @ 16:15:56.056
  
  
                          SELECT * FROM guest_booking WHERE room_id = 
  
                                      ?
  
                                   and check_in &lt;= 
  
                                      ?
  
                                   and check_out &gt; 
  
                                      ?
  
                                   and status != 0
  
  Query Parameter Value(s) -
  Parameter #1(cf_sql_integer) = 56
  Parameter #2(cf_sql_date) = {ts '2011-11-14 00:00:00'}
  Parameter #3(cf_sql_date) = {ts '2011-11-14 00:00:00'}
  </code></pre>
  
  <p>Many thanks in advance..</p>
  
  <p>Jason</p>
  
  <p><strong>EDIT AFTER SHAWN'S ANSWER BELOW</strong></p>
  
  <p>Have changed the following two lines of query preperation:
  query name is now different for differ query.. created dynamically from paramaters passed in</p>
  
  <pre><code>q.setName("check#arguments.room_id##DateFormat(arguments.date,'ddmmyy')#");
  </code></pre>
  
  <p>createTimeSpan removed from quotes, so not passed in as a string.</p>
  
  <pre><code>q.setCachedWithin(createTimespan(0, 1, 0, 0));
  </code></pre>
  
  <p>I have also tried sending through an unprepared query (not using addparam(), but just rendering the variables straight in the query string), but made no difference..</p>
  
  <p><strong>EDIT 2 AFTER SHAWN'S 3rd EDITANWSER BELOW</strong>
  Shawn.. nice pickup on edit 3!!! you have isolated where the problem is.  (anyone reading this, quick, up vote Shawn's answer, he found a needle in a hay stack)</p>
  
  <p>Passing the dates in as params does not cache..e.g..</p>
  
  <pre><code>q.setSQL("SELECT booking_id FROM guest_booking WHERE room_id = :roomID and check_in &lt;= :iDate and check_out &gt; :iDate and status != 0");
  q.addParam(name="iDate", value="#createODBCDate(arguments.date)#", cfsqltype="cf_sql_date");
  </code></pre>
  
  <p>Just passing it in as a variable does not cache..e.g..</p>
  
  <pre><code>q.setSQL("SELECT booking_id FROM guest_booking WHERE room_id = :roomID and check_in &lt;= #createODBCDate(arguments.date)# and check_out &gt; #createODBCDate(arguments.date)# and status != 0");
  </code></pre>
  
  <p><strong>BUT</strong> hard coding the dates DOES cache..e.g..</p>
  
  <pre><code>q.setSQL("SELECT booking_id FROM guest_booking WHERE room_id = :roomID and check_in &lt;= {ts '2011-12-16 00:00:00'} and check_out &gt; {ts '2011-12-16 00:00:00'} and status != 0");
  </code></pre>
  
  <p>This is all good, but clearly I can't hard code the dates...  The dates will change for each day obviously, but even where I run the same query with the same dates being passed in dynamically (query syntax being exactly the same), the query won't cache if the dates are passed in as variables.. only if they are hard coded into the query..  wierd.. will keep playing and see what I can find.</p>
  
  <p>Thank you Shawn for pinpointing the problem !!!</p>