Query caching places the results of a database query into ColdFusion Server’s memory where it can be instantly retrieved without querying the database. Query caching is one of the most useful tools for enhancing performance in your ColdFusion applications; unfortunately, it is also one of the least used. A few caveats are involved in query caching, but all in all, it is a very straightforward technique to employ. You should consider caching only queries that fit the following criteria:

The query has a significant allowable latency. In other words, the data in the result set doesn’t change often and is, therefore, stable over a significant length of time.

The query is universal in scope, such that all users of your application can utilize exactly the same query result.

The query requires significant resources to process. Another way to look at it is as follows:

The latency gives you permission to use caching.

The universality of scope makes caching possible.

The processing requirements make caching necessary. Whenever you cache a query, you place a copy of it in memory and instruct ColdFusion MX to retrieve the memory-based version of that query for a specified length of time. For example, the code hows a CFQUERY that caches its result set for 12 hours, after which another call to CFQUERY retrieves data directly from the database and caches that new result set in memory.

<cfquery name=”CacheFor12Hours”
   datasource=”CFMXBible”
   cachedwithin=”#CreateTimeSpan(0,12,0,0)#”>
   SELECT
   LastName + ‘, ‘ + FirstName AS UserName
   FROM
   AppUser
   ORDER BY
   UserName ASC
   </cfquery>
   <cfoutput query=”CacheFor12Hours”>
   #UserName#<br>
 </cfoutput>

The code shows a CFQUERY that caches its result set after 1 a.m. on July 4, 2002; before this time, a call to CFQUERY retrieves its data directly from the database.

<cfquery name=”CacheAfterDate”
   datasource=”CFMXBible”
   cachedafter=”07/04/2002 1:00:00”>
   SELECT
   LastName + ‘, ‘ + FirstName AS UserName
   FROM
   AppUser
   ORDER BY
   UserName ASC
   </cfquery>
   <cfoutput query=”CacheAfterDate”>
   #UserName#<br>
 </cfoutput>

CachedWithin is the more commonly used of the two available caching attributes. CachedAfter has very few practical uses; it is mainly used to wait until a specified time when new data becomes available to your database from some external process and then to cache the new data. Good candidates for caching queries are as follows:

The online-store catalog pages that display the thumbnails and descriptions of the categories of items sold.

The online-store catalog pages that display each specific category’s items.

The list of items on sale in an online store.

Select menus that are dynamically populated with infrequently changed data, such as the names of department managers.

Take a look at your current applications and see whether you can find opportunities for query caching. Just make sure that you ask your clients how latent the data produced by each query can realistically be before you cache it. Don’t forget where a cached query resides: in memory. If you don’t have enough physical memory to easily handle all your cached queries, your operating system pages memory out to disk, and the performance gains that you hoped for are greatly diminished. CFQUERYPARAM creates what are called bind parameters in your query that bind to structures in your database through the database driver. If your query is cached, you have no database structure to which anything can bind after the CFQUERY call is made. This is one reason why you can’t use CFQUERYPARAM with cached queries. The other reason is one of internal identification. ColdFusion MX identifies a cached query in memory by its signature, which is the textual version of the SQL code itself. After ColdFusion MX receives a CFQUERY call with a CACHEDWITHIN or a CACHEDAFTER clause specifying a time value within the caching period, it looks in memory for a query object using the same SQL statement. If the statement requested is identical in every way to the version in memory, the memory-based version of that query’s result set is returned to ColdFusion, and the database is bypassed entirely. Bind parameters obfuscate those portions of the SQL code that are likely to change between queries—namely, the ColdFusion variables that are directly resolved into plain text by ColdFusion’s interpretation engine if CFQUERYPARAM isn’t present. So comparing an SQL statement containing a CFQUERYPARAM tag against a plain-text SQL statement is impossible, because you have no way to tell whether anything inside the query’s CFQUERYPARAM has changed. CFSTOREDPROC does not enable you to cache a result set produced by a stored procedure. But you can cache it if you call your stored procedure by using a special syntax within CFQUERY, as shown in the code.

<cfquery name=”CacheFor12Hours”
   datasource=”CFMXBible”
   cachedwithin=”#CreateTimeSpan(0,12,0,0)#”>
   {CALL sp_GetCompanies(‘GA’)}
   </cfquery>
   <cfoutput query=”CacheFor12Hours”>
   #CompanyName#, #ZipCode#<br>
 </cfoutput>

This technique works only for stored procedures returning a single result set. If your stored procedure returns more than one result set, you must forego query caching and call your stored procedure by using CFSTOREDPROC. In this article, you’ve learned how to understand and, most importantly, visualize how SQL is processed internally, including joins, grouping, and aggregate functions. You’ve also learned how to leverage structured exception handling for routing ColdFusion application behavior based on database errors thrown when a user attempts a forbidden action. Finally, you learned how to improve the performance of your ColdFusion application by caching database result sets in memory. I sincerely hope this article has helped you better understand SQL as a descriptive language through the visualization examples that we’ve given. You can memorize syntax until you’re blue in the face, but just as in any professional endeavor, if you can’t “feel it,” you can’t perfect it. Always visualize relational result sets as a series of simple two-table joins and your types of joins as Venn diagrams. If you picture the intermediate work product of each clause of your SQL statement as it is produced and then fed to the next clause in the processing order of precedence, you can never get lost again. Similarly, listen to the English-language version of your SQL statement and ask yourself whether it makes sense. Are you asking for two different things of different dimensions in the same statement? If so, your statement always fails because the question isn’t possible to answer. Remember that GROUP BY conceptually produces an invisible third dimension containing the constituent rows that make up each group and that aggregate functions operate over each individual group rather than the entire set of data. If you design your database by using all of the principles taught earlier you can expect your database to throw errors whenever users attempt to violate the rules that you built into it. Knowing where to catch these database errors in your application and how to handle them simply as exceptions to normal workflow makes your ColdFusion code smaller, easier to write, and more manageable. Finally, find the places where data doesn’t need to be “live” all the time, but can lag for a few hours or days before being refreshed, and turn these segments into cached queries. Here you learn the principles and techniques that really make a database perform to its fullest potential. Because of the scope of this article, the topics in this article can be touched on only summarily. To fully understand them requires many hours of in-depth reading outside this article. My goal is to teach you the basics, give you a solid understanding of how they are correctly used with ColdFusion applications, and encourage you to read further in articles that treat these topics with the depth and detail they deserve. For the internal mechanics of SQL Server 2000, we recommend Inside Microsoft SQL Server 2000 by Kalen Delaney (Microsoft Press). For writing stored procedures and triggers in Transact/SQL, the language used by SQL Server 2000, we recommend Advanced Transact-SQL for SQL Server 2000. These techniques apply mostly to full-fledged database servers and not to desktop databases such as Microsoft Access. Desktop databases do not have the advanced features such as stored procedures and triggers that are covered in this article. This article is intended to be run on SQL Server 2000 only, except where Oracle 9i techniques are addressed. This article covers transactions, why they are very important to solid database design, multiuser concerns that you must consider in designing transactions, and how transactions are controlled. A transaction is an indivisible action (also known as an atomic action, a least unit of work, or a single unit of work). No matter how many individual data manipulations occur within the transaction, it’s all or nothing: If all the individual manipulations execute correctly, the transaction is successful; if any one of the individual manipulations fail, the entire transaction fails. The most common example of a transaction involves the ATM at your bank. If you request $20 from the machine, it should both give you a twenty-dollar bill and remove $20 from the balance in your account. Both actions must occur for the transaction to be valid. If only one of them occurs, someone’s going to be pretty upset! Now look at a database transaction. The codecreates an order item on a sales order and also removes the amount ordered from the AvailableToSell column of the InventoryItem table.

<cftransaction>
   <cfquery name=”InsertOrderItem”
   datasource=”CFMXBible”>
   INSERT INTO OrderItem (
   SalesOrderID,
   ItemNumber,
   Description,
   UnitPrice,
   Quantity
   )
   VALUES (
   1,
   ‘CAS30-BLK’,
   ‘30-Minute Cassette, Black Case’,
   1.05,
   10
   )
   </cfquery>
   <cfquery name=”UpdateInventory”
   datasource=”CFMXBible”>
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell - 10
   WHERE
   ItemNumber = ‘CAS30-BLK’
   </cfquery>
 </cftransaction>

Everything between the opening and closing CFTRANSACTION tags is considered a single transaction. For this transaction to succeed, both the insert into the OrderItem table and the update of the InventoryItem table must succeed. That’s the whole idea of a transaction: all or nothing. If both queries are successful, the transaction commits, and the effects of both queries are written to the database. If either query fails, the entire transaction is rolled back as if nothing ever happened. In the example in the preceding part, ColdFusion supplies the commands to control the beginning and end of the transaction, but in many cases, such control is handled at the database server itself using native database commands. So where should you control a transaction? The simple answer to this question is always “At the database server, if you possibly can.” The reason for this answer is the database’s capability to control its transactions by using native commands and capabilities instead of relying on the database driver’s capability to communicate transactional control to the database. Some database drivers do not have the capability to pass transactional control commands to your database, so in these cases, you need to encapsulate all transactional controls within a stored procedure on the database and simply call it from ColdFusion. Some other drivers can communicate only a subset of the database’s available transactional control commands and are, therefore, not as capable a solution. Look now at a transaction that is controlled within the stored procedure itself. This code is the equivalent of the other, except that the transaction is controlled within the stored procedure rather than in ColdFusion.

BEGIN TRANSACTION
   INSERT INTO OrderItem (
   SalesOrderID,
   ItemNumber,
   Description,
   UnitPrice,
   Quantity
   )
   VALUES (
   1,
   ‘CAS30-BLK’,
   ‘30-Minute Cassette, Black Case’,
   1.05,
   10
   )
   IF @@ERROR != 0
 BEGIN

RAISERROR 50001 ‘The OrderItem could not be inserted.’
   ROLLBACK TRANSACTION
   RETURN
   END
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell - 10
   WHERE
   ItemNumber = ‘CAS30-BLK’
   IF @@ERROR != 0
   BEGIN
   RAISERROR 50002 ‘The InventoryItem could not be updated.’
   ROLLBACK TRANSACTION
   RETURN
   END
 COMMIT TRANSACTION

The syntax is a little different, but the principles are very similar, aren’t they? It’s really just a matter of learning both methods for implementing transactions and then controlling them as close to the database server as your application enables you to do so.