Beginners Guide to Memory Based Caching

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

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

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.

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.

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:

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!