Macromedia - Developer Center : Caching Queries to Disk or to Memory with ColdFusion

As most ColdFusion developers know, Macromedia ColdFusion provides a built-in mechanism for caching queries. Adding a simple cachedWithin attribute to any cfquery tag in ColdFusion lets you cache a query in memory for a specified time.

If you have ever cached a query using this simple built-in functionality, you are certainly familiar with the challenges and limitations that result from implementing query caching with this method. This type of query caching uses a ColdFusion Administrator setting that limits the maximum number of queries that can exist in the cache at any given time. Additionally, clearing cached queries with this method is a much more difficult task than you would expect. In this article, I explain a custom tag approach for caching queries to memory or to disk, providing more flexibility than built-in ColdFusion methods.

Before continuing any further, note that that there is no substitute for solid database design when it comes to query performance. Often times, you can improve query performance through simple revisions to your SQL statement or taking advantage of the power of your RDBMS by adding database enhancements such as indexes to queried columns. Additionally, the alternative method of caching queries to disk described in this article should not be considered a “best practice,” not to mention the fact that you can add memory cheaply to servers based on current RAM prices.

Consider my method of caching queries to disk (one of the options with my custom tag) as a workaround for certain scenarios you might be facing as a developer. I originally developed the custom tag in this article for a client of mine where, at the time, I did not have the option of altering the database design to improve query performance. Additionally, many of the queries where I applied this custom tag involved complex SQL logic including aggregate functions and a high number of nested inner/left joins across a number of tables. While the resulting final record sets were small to medium size, the query execution was unbearably slow and only got worse with heavier server load. In these cases, I was able to apply this custom tag and drastically improve upon the original execution time of the query. In other cases, however, I found that this custom tag actually executed slower than the original query because the WDDX packets written to disk were so large—as a result of very large record sets or even queries with many large columns. As a developer, always evaluate query performance on a case-by-case basis before and after applying this caching solution.