Caching Queries to Disk or to Memory with ColdFusion

A New Method for Caching Queries

To circumvent the limitations of ColdFusion's built-in query caching method, I developed a custom tag solution that lets you cache queries to memory or to disk. The slight drawback of caching to disk, however, is that caching to disk is often comparatively slower than caching to memory, due to server interfaces and bus speeds.

Introducing the cf_extremeQueryCache Custom Tag

The inspiration for my cf_extremeQueryCache tag came from another great custom tag designed for content caching: cf_superCache. You can download the cf_extremeQueryCache tag from page one of this tutorial. If you have not yet added cf_superCache to your development arsenal, I recommend doing so. You can find the cf_superCache tag by searching the keyword in the Macromedia ColdFusion Exchange. Additionally, Brandon Purcell is currently developing a content caching tag called cf_Accelerate, also inspired by cf_superCache which you can download from his website.

You can use cf_extremeQueryCache around any ColdFusion query in order to cache the query either to memory or to disk. The resulting query cache contains the original query name, the date/time that the cached query will expire, and the returned recordset.

If you're looking for specific details on how the cf_extremeQueryCache tag functions or how to use it, open the tag in Macromedia ColdFusion Studio or in Dreamweaver Code view (or your ColdFusion editor of choice) and spend some time reading the opening comments, all 175 lines of them. The opening comments document the specific functionality of the custom tag, but I will give you a quick overview below.

Implementing cf_extremeQueryCache

Use the following simple steps to install and use the custom tag.

Step 1: Installing the cf_extremeQueryCache Custom Tag

Copy cf_extremeQueryCache.cfm into your ColdFusion custom tag directory at cfusionmx/customtags (or cfusion/customtags directory, if you're using an older version of ColdFusion). Once you copy the extremeQueryCache.cfm file into the customtags directory, all of your ColdFusion web applications can access the cf_extremeQueryCache custom tag. This is a one-time step; you do not need to repeat this step every time you would like to use the custom tag. If you're a JRun user, read all about Creating and Using Custom Tags in the ColdFusion LiveDocs.

Step 2: Identifying a Query for Caching

This step really is subjective, since each developer will have his own opinion on what makes a query a good caching candidate. However, your criteria for selecting a query to cache will be different than it has been in the past since this custom tag can cache any number of queries in memory or onto disk.

Since the ColdFusion Administrator setting, Maximum number cached queries, limits ColdFusion's built-in query caching methods, the criteria for selecting a query to cache previously often met the following conditions:

• A query used on multiple pages throughout a web application
• A recordset that does not change frequently
• A recordset that is the same for all users
• A query that has a very limited number or no input parameters affecting its recordset

For example, the following query retrieves a list of state names and abbreviations; this is a good candidate for caching through ColdFusion's built-in query caching method.

<cfquery name="qStates" datasource="myDSN">
	select strAbbreviation, strName
	from tblStates
	order by strName
</cfquery>

Conversely, since available server RAM and disk space are the only limitations for the cf_extremeQueryCache custom tag, the criteria for selecting queries to cache becomes much more flexible:

• A query that executes slowly because of complex SQL logic or because it retrieves a large recordset
• A query that has input parameters which affect the recordset it returns

For instance, the following query returns all titles that a user has access to and which begin with a letter that the user selected:

<cfquery name="qUserTitles" datasource="myDSN">
	select
		t.uuidTitle,
		t.strTitle
	from
		tblTitle t,
		tblXREF_Title_User xtu,
		tblUser u
	where
		u.user_id = #request.user_id#
	and	u.user_id = xtu.user_id
	and	xtu.title_id = t.title_id
	and	t.strTitle like '#form.strFirstLetter#%'
</cfquery>

Step 3: Creating a Query Caching Directory Structure

Note: Applies to disk caching method only. Skip to Step 4 if you want to cache to memory.

If you choose to have the cf_extremeQueryCache custom tag write cached queries to disk, you must create one or more directories to store the cached queries. I strongly recommend reviewing the Caution When Caching to Disk section of this article before choosing to cache queries to disk.

When using the cf_extremeQueryCache custom tag to cache your queries to disk, I recommend the following:

1. Create an extremeQueryCache directory outside of your web document root.
2. Create a subdirectory in the extremeQueryCache directory for each query you plan to cache, preferably using the query's name when naming the subdirectory.

As a security precaution, it's important that you put all your cached queries outside of the web document root if you will be caching security-sensitive information. If users find your query caching directories while poking around your website through their web browser, then they could download the WDDX files, snoop through them, and in short, browse through your database records.

One example of recommended directory structure is as follows. As with the qUserTitles query above, I created two directories:

• C:\cfusionmx\extremeQueryCache\
• C:\cfusionmx\extremeQueryCache\qUserTitles

In cases where you are running more than one web application under the same instance of ColdFusion, consider creating directories for each of your applications inside of the extremeQueryCache directory, placing query directories beneath their corresponding application. This will help prevent problems that might arise if you begin caching queries with the same name across multiple applications. In this case, your directory structure would be as follows:

• C:\cfusionmx\extremeQueryCache\
• C:\cfusionmx\extremeQueryCache\myAppName\
• C:\cfusionmx\extremeQueryCache\myAppName\qUserTitles

By default, the cf_extremeQueryCache custom tag will throw an error if the cache directory specified in your tag attribute does not already exist on the file system. Since I included the custom tag file in this article, you could modify the custom tag's code to dynamically create cache directories rather than throw an error. When I created the tag, I wanted explicit control of the location and creation of query caching directories, so I did not build this functionality into the tag.

Step 4: Applying cf_extremeQueryCache to a Query

To use the tag, simply wrap it around the CFQUERY that you would like to cache and specify a few attribute values.

<cf_extremeQueryCache
	queryName=""
	cacheName=""
	cachedWithin=""
	cacheScope|cacheDirectory=""
>
<cfquery name="qUserTitles" datasource="myDSN">
	select
		t.uuidTitle,
		t.strTitle
	from
		tblTitle t,
		tblXREF_Title_User xtu,
		tblUser u
	where
		u.user_id = #request.user_id#
	and	u.user_id = xtu.user_id
	and	xtu.title_id = t.title_id
	and	t.strTitle like '#form.strFirstLetter#%'
</cfquery>
</cf_extremeQueryCache>

queryName

The queryName attribute specifies the name of the query that you are caching between the tag's open and close brackets. In this case, the queryName attribute value is qUserTitles.

<cf_extremeQueryCache
	queryName="qUserTitles"
	...
>
...
</cf_extremeQueryCache>

cacheName

This is the trickiest tag attribute. The cacheName attribute specifies a value that uniquely identifies each possible recordset that the query could return. The simplest way to create a unique value is to create a concatenated string that combines the input parameter values in the query. In the example, there are two input parameters (surrounded by pound signs) that affect the record returned by the query: request.user_id and form.strFirstLetter. Therefore, the cacheName attribute value would be:

<cf_extremeQueryCache
	queryName="qUserTitles"
	cacheName="qUserTitles_#request.user_id#_#form.strFirstLetter#"
	...
>
...
</cf_extremeQueryCache>

cachedWithin

If you're familiar with ColdFusion built-in query caching, then this parameter is no mystery. The value of this parameter specifies how long the server will use the cached query results before running the query again and generating a new cached query.

Determine the cachedWithin value on a case-by-case basis for each query you cache. Consider how often a query's recordset will change. A query that returns state names is unlikely to change, so it's safe to cache the query for a long time. However, a query to an "orders" table for an e-commerce site will likely change all the time as customers submit new orders.

In the qUserTitles example, the query returns a list of titles beginning with a letter that a user selects. Since the titles catalog doesn't change often, caching the query on a daily basis is sufficient.

Set the cachedWithin parameter with a time span using the ColdFusion createTimeSpan() function. Since the function takes parameters of days, hours, minutes, and seconds, you can set the value as follows:

<cf_extremeQueryCache
	queryName="qUserTitles"
	cacheName="qUserTitles_#request.user_id#_#form.strFirstLetter#"
	cachedWithin="#createTimeSpan(1,0,0,0)#"
	...
>
...
</cf_extremeQueryCache>

cacheScope or cacheDirectory

If you are caching the query to memory, use the cacheScope parameter. If you are caching the query to disk, use the cacheDirectory parameter. When using the custom tag, pass only one of these parameters.

This attribute specifies which variable scope to store the cached query in. You can specify a value of server, application, or session. The cached query will be placed in the specified scope as part of a structure called stcExtremeQueryCache.

<cf_extremeQueryCache
	queryName="qUserTitles"
	cacheName="qUserTitles_#request.user_id#_#form.strFirstLetter#"
	cachedWithin="#createTimeSpan(1,0,0,0)#"
	cacheScope="application"
>
...
</cf_extremeQueryCache>

This attribute specifies the full directory path of the cache directory.

<cf_extremeQueryCache
	queryName="qUserTitles"
	cacheName="qUserTitles_#request.user_id#_#form.strFirstLetter#"
	cachedWithin="#createTimeSpan(1,0,0,0)#"
	cacheDirectory="c:\cfusionmx\extremeQueryCache\qUserTitles\"
>
...
</cf_extremeQueryCache>

Step 5: Measuring the Impact

At this point, you could save the changes to your code, point your web browser at the page, and fire away. However, I think it's important to take a moment and measure the performance gain you achieve by using this custom tag query caching method. After all, the proof is in the pudding, right? Because I'm certain that pulling a query from the cache will be much faster than executing the query every time, I've added a built-in method for measuring the performance gain, so you can see the difference for yourself.

By simply adding the debugMode attribute with a value of true to the cf_extremeQueryCache tag call, the tag will automatically display a JavaScript alert indicating the execution time (in milliseconds) and whether the server executed the query or retrieved it from cache. Of course, remove the debugMode attribute from your code before releasing your changes to production.

<cf_extremeQueryCache
	queryName="qUserTitles"
	cacheName="qUserTitles_#request.user_id#_#form.strFirstLetter#"
	cachedWithin="#createTimeSpan(1,0,0,0)#"
	cacheScope="application"
	debugMode="true"
>
...
</cf_extremeQueryCache>

Limitations

If you choose to cache queries to memory, I recommend you keep a close watch on your server memory usage once you apply this tag. Since this tag will continue to place more and more cached queries into memory, there is the risk that an extremely large volume of cached queries could drain your server's available RAM and cause overall performance issues for the server. To avoid this, consider running a nightly ColdFusion scheduled task to clear any cached queries from memory (see opening comments of the cf_extremeQueryCache custom tag for details on clearing cached queries).

If you choose to cache queries to disk, consider the following points. While available disk space is the primary limitation of this caching method, there is another limitation not immediately evident. In developing and testing this custom tag, I found another limitation that directly affects the successful implementation of this custom tag when caching queries to disk. While I've not extensively tested it, I believe this issue only affects users running on ColdFusion MX. The limitation has to do with an internal server setting that governs the "entity expansion limit" of ColdFusion. When caching to disk, this custom tag creates WDDX packets storing all the cached query information. If ColdFusion attempts to deserialize a WDDX packet that exceeds the "entity expansion limit," the server throws an error and is unable to deserialize the packet. Because my custom tag uses WDDX packets heavily when caching to disk, with the potential of exceeding the limit, I ran into a problem. Fortunately, I have planned for this particular situation, should you encounter it.

If the cf_extremeQueryCache tag attempts to deserialize a disk-based WDDX packet that exceeds this limit, the tag will throw a custom error message explaining the cause of the error and the steps for increasing the "entity expansion limit," eliminating the problem. Check out Technote 19125: ColdFusion MX 6.1: Error parsing large XML documents, for more information.

If you'd like to eliminate the potential limitation before encountering it, read on. The default value of the "entity expansion limit" is 64000. You can modify this value by adding the following JVM argument in the ColdFusion or JRun Administrator (depending on how you have installed ColdFusion). You must add the argument with the leading "-", preceded by a space, if there are already other JVM arguments present, as follows:

-DentityExpansionLimit=128000

You must decide what the new argument value will be. I simply used 128000 to double the default value.

For this change to take effect, you must restart ColdFusion. If cf_extremeQueryCache continues to throw the error, try a larger value for the argument, restart ColdFusion, and test it again until you eliminate the error.

Page 3 of 5

Previous Page

Next Page