Beginners Guide to Memory Based Caching
Posted By : todd sharp Posted At : October 2, 2006 3:00 PM Posted In: ColdFusion
2
In my opinion, one of the coolest "advanced" concepts of ColdFusion is memory based query caching. Sure, caching may not make it into the Cool Things to Do In ColdFusion Hall of Fame, but it is close to the top of my list for the simple fact that caching is a quick and easy way to drastically improve performance. In this post I'd like to talk about memory based query caching to store data returned from a stored procedure. Obviously this method has certain limitations and should only be used when and where approriate (for example, you certainly would not cache resultsets where there is a need for 'live' data). Since caching and performance are always hot topics in the ColdFusion community, I welcome any and all feedback/suggested alternatives/warnings in the comments (just keep it friendly).
Assumptions:
You have been requested to create a new report. The report is based on a stored procedure which has already been created for you. The stored proc returns three resultsets, each with summary data that need to be displayed to the user. The proc runs in just over 2500ms. The data is not required to be 'live' - day old data is 'ok'.
Step 1 - Set Up A Container For The Query
First I set up a struct in my application.cfc file to house the cached queries.
application.cfc
<cffunction name="onApplicationStart">
<!--- instatiate the cfc --->
<cfset application.theCFC = createObject("component", "path.to.theComponent")>
<cfset application.cachedQueries = structNew()>
<cfset application.cachedQueries.resultOne = queryNew("")>
<cfset application.cachedQueries.resultTwo = queryNew("")>
<cfset application.cachedQueries.resultThree = queryNew("")>
</cffunction>
<!--- other stuff... --->
Step 2 - Creating the Query
The second step I would take is to create a method within a cfc to retrieve the data (or create a new cfc if absolutely necessary). The method is a simple one:
theComponent.cfc
<cfset var returnStruct = structNew()>
<cfset var resultOne = queryNew("")>
<cfset var resultTwo = queryNew("")>
<cfset var resultThree = queryNew("")>
<cfstoredproc datasource="#variables.dsn#" procedure="storedProcName">
<cfprocresult name="resultOne" resultset="1" />
<cfprocresult name="resultTwo" resultset="2" />
<cfprocresult name="resultThree" resultset="3" />
</cfstoredproc>
<cfset returnStruct.resultOne = resultOne>
<cfset returnStruct.resultTwo = resultTwo>
<cfset returnStruct.resultThree = resultThree>
<cfreturn returnStruct/>
</cffunction>
Step 3 - Caching the Query
There are a few alternatives at this point. Assuming the performance of the query is not absolutely horrible, I could have simply invoked the getTheDataToCache method in the onApplicationStart method of the application.cfc and been done. From there it would simply be a case of outputting the application variable (or querying it with QofQ if needed). Lets assume though that the proc isn't the quickest. In that case, I'd rather not run this onApplicationStart. Instead, I like to create a separate template which invokes the component and caches it. I then schedule the execution of this template during off hours.
<cfset theDataStruct = application.theCFC.getTheDataToCache()>
<!--- first lock the scope --->
<cflock scope="application" timeout="30">
<!--- store each recodset in the application.cachedQueries struct --->
<cfset application.cachedQueries.resultOne = theDataStruct.resultOne>
<cfset application.cachedQueries.resultTwo = theDataStruct.resultTwo>
<cfset application.cachedQueries.resultThree = theDataStruct.resultThree>
</cflock>
Step 4 - Accessing/Outputting The Query
From here, the query can be accessed just like any other query - the tricky thing for a beginner is the fact that the query at this point is buried pretty deep in the application scope.
<cfdump var="#application.cachedQueries.resultOne#">
<!--- query the query --->
<cfquery name="yetAnotherStinkingQuery" dbtype="query">
select stuff
from application.cachedQueries.resultOne
where stuff = 'other stuff'
</cfquery>
It may look funny to access a query column as application.cachedQueries.resultOne.columnName but it's really the same as theQuery.columnName. You could also always set "theQuery" equal to the cached query so that it could be directly referenced without the extended dot notation, for example:
<cfoutput>
#theQuery.columnName#
</cfoutput>
So that's my technique for memory based query caching. As I said above, be careful when and where you use this technique.
Again, any suggestions/alternatives are welcomed!
The code on the page is unreadable, is that normal?? I've never noticed it before. I'm using FF.
Thanks for reminding me about caching, I totally forgot about it. This method will also be useful for sites that using a database to lay out the navigation bar.
Ryan
todd (at) cfsilence (dot) com
haven't set up the gmail yet!