Simple Query Caching

There are times when data from queries doesn't change very often and therefore can be saved in memory.  This eliminates a trip to the database and accessing the data out of memory is much faster.  There are two ways to handle this using the <cfquery> tag.

The first way to cache a query is using the the cachedAfter attribute of the <cfquery> tag.  The cachedAfter attribute takes a date or date and time and the query will be cached at any point after that date and time.  It will remain cached until the cache is flushed, the date or date and time value in cachedAfter changes, or the server is restarted. This stratey works well for data that changes daily.  A good example would be a report that contains large amounts of data that updates once a day. 

<cfset cachedAfterValue = CreateDateTime( 2009,11,11,6,0,0 )
/>

<cfquery name="qGetAGinormousOfData"
cachedAfter="#cachedAfterValue#" datasource="dsn" >
SELECT
*
FROM
tbl_GinormousAmountOfData
</cfquery>

The application would need to have logic added to handle changing the date value each day.  In this instance the query will be cached after 6:00 AM on November 11th, 2009.

The second way to cache a query is using the cachedWithin attribute of the <cfquery> tag.  The cachedWithin attribute takes a timespan as a value created using the CreateTimeSpan function.  The query will be cached for the amount of time specified in the time span.  It will remain cached until the length of the timespan is reached, the cache is flushed or the server is restarted.  This works well for queries that don't change often.  A good example of this would be a list of states.  

<cfquery name="qGetStates" cachedWithin="#CreateTimeSpan(
4,20,6,13 )#" datssource="dsn">
SELECT
*
FROM 
tbl_States
ORDER BY
stateName
</cfquery>

The query above will be cached for 4 days, 20 hours, 6 minutes and 13 seconds!  Once that time interval expires, the next user to run the query will run the actual query and then it will be cached again.

ColdFusion makes query caching very simple.  While this is not the only way to store queries in memory, it is certainly a good place to start.  And in most cases will be all an applicaiton needs.