In Listing 24.9, the code returns a recordset containing the state abbreviation and the state ID from the State table. As you might imagine, this query could be used to populate a form select list. By using the results of our ListStates query to populate a select list, we remove the need to hard code 50 or more OPTION tags. Not only does this save us time, but also it eliminates potential mistakes in our code. Yes, I know that there are only 50 states, but let's not forget about Washington, D.C., Puerto Rico, and the various territories.
I am sure that you can think of a number of templates that could require the use of this States select list. Let's assume that we have created a form that new customers use to open new accounts. Each time a customer goes to create a new account, the ListStates query is run, and the result set is the same each time. Although this is a simple query and takes only a few milliseconds to execute, it could be called hundreds of times a day as new accounts are created. Because the ListStates query could be used in a number of CFM templates, we could realistically have a situation in which this query is executing several thousand times a day. Those "few milliseconds" of execution time start adding up, not to mention the resulting network traffic.
Fortunately, ColdFusion offers the capability to cache in server memory the result sets of specified SQL queries. It does this with two optional attributes of the CFQUERY tag: cacheWithin and cacheAfter. The cachedWithin attribute is used to specify a time span for caching the query results. The CreateTimeSpan function creates a time span from the present backward. If the original query was executed within the specified time span, the recordset in memory is used. If not, the query is executed and the new recordset is placed in memory.
Listing 24.10 Query Caching Using the CACHEDWITHIN Attribute
<cfquery name="GetStates" datasource="MyTest" cachedWithin=#CreateTimeSpan(0,1,0,0)#> SELECT StateAbbreviation,StateName FROM State WHERE StateName LIKE 'C%' </cfquery>
The CACHEDAFTER attribute caches the query based on a particular date and time. When a call for the query is made, ColdFusion verifies whether a recordset of the query exists. If it does, ColdFusion checks the timestamp of the cached recordset and if it is after the time specified in the CACHEDAFTER attribute, it uses the existing recordset. There is one important difference between the CACHEDWITHIN and CACHEDAFTER attributes. The CACHEDWITHIN attribute eventually expires; the CACHEDAFTER attribute does not.
Listing 24.11 Query Caching Using the CACHEDAFTER Attribute
<cfquery name="GetStates" datasource="MyTest" cachedAfter="04/25/02 14:32"> SELECT StateAbbreviation,StateName FROM State WHERE StateName LIKE 'C%' </cfquery>
At this point, we should point out a few quick tips concerning the "date" for the cachedAfter attribute. The date can be in different formats, including the following:
The time of 00:01 is assumed if a time is not specified. You can use either the 24-hour or a.m./p.m. time formats:
Figure 24.3 shows our query being executed without query caching being utilized. Be sure to note the query execution time in the QUERY debug output.
Figure 24.3. GetStates without query caching.
Now look at Figure 24.4, which is our query executed with the cachedWithin attribute. Again, be sure to look at the execution time in the QUERY debug output. You can see that the execution time has changed from 130ms to Cached Query.
Figure 24.4. GetStates with query caching.
Query caching must be enabled in the ColdFusion Administrator for the CACHEDAFTER and CACHEDWITHIN attributes to be functional. You enable caching in the ColdFusion Administrator by setting the value for "Limiting the maximum number of cached queries on the server to" to a value greater than 0 (see Figure 24.5).
Figure 24.5. ColdFusion MX Administrator caching settings.
Queries are cached on a serverwide basis, not per application. Prior to the release of ColdFusion 4.5, you were limited to a maximum of 100 cached queries. The removal of this limit does not mean you should attempt to cache all the queries in your application. Remember that cached queries are stored in the server memory, so the queries and number of queries that you cache can impact overall server performance.
ColdFusion uses the FIFO (First In, First Out) model to manage cached queries when the limit is reached. This means that when the limit for cached queries is reached, the oldest cached query is dropped to make room for the new cached query.
There are a couple things to keep in mind when caching queries. For caching purposes, queries must use same SQL statement, datasource, query name, username, password, and DBTYPE. There are three sample queries. ColdFusion treats all three differently, even though they appear to be identical at first glance.
Listing 24.12 GetStates Examples 1 3
Example 1 <cfquery name="GetStates" datasource="MyTest" cachedWithin=#CreateTimeSpan(0,1,0,0)#> SELECT StateAbbreviation,StateName FROM State WHERE StateName LIKE 'C%' </cfquery> Example 2 <cfquery name="GetStates" datasource="MyTest2" cachedWithin=#CreateTimeSpan(0,1,0,0)#> SELECT StateAbbreviation,StateName FROM State WHERE StateName LIKE 'C%' </cfquery> Example 3 <cfquery name="GetStates" datasource="MyTest" cachedWithin=#CreateTimeSpan(0,1,0,0)#> SELECT StateAbbreviation, StateName FROM State WHERE StateName LIKE 'C%' </cfquery>
Examples 1 and 2 have different datasources, which cause ColdFusion to treat Example 2 as a new query. Still trying to see the difference between Example 1 and 3? Look closely at the select statement in Example 3; notice the space after the comma separating StateAbbreviation and StateName. You must pay particular attention to the SQL statement because something as small as tabbing causes ColdFusion to treat what appear to be identical SQL statements as different SQL statements. Being "doubting developers," we had to prove the tabbing to ourselves when we first worked with query caching in ColdFusion.
Compare the executed SQL statement in Figure 24.3 and Figure 24.4 with Figure 24.6. Notice that there is a space in the SELECT statement in Figure 24.6; that it is the only difference.
Figure 24.6. SELECT statement with a space.
Up to this point, we have used examples of static queries for our discussion of query caching. Now, what about the use of query caching with dynamic SQL? There is nothing to prevent you from caching queries that contain dynamic SQL, but you should exercise some caution. ColdFusion caches a recordset for each dynamic value. If we modify our ListStates query to cache, we most likely have only one recordset cached unless we have multiple applications on our server using the same query.
Listing 24.13 Caching a Dynamic Query
<cfquery name="ListStates" datasource="#DSN#" cachedWithin=#CreateTimeSpan(0,1,0,0)#> SELECT StateID,StateAbbreviation FROM State </cfquery>
If we add the cacheWithin attribute to the GetStates query found in Listing 24.2, we easily could have dozens of cached recordsets. This is not to say that you should not use caching with dynamic SQL queries other than with scenarios similar to Listing 24.13. What we are pointing out is that caution should be used.
Another thing to consider when using caching queries is that cached queries do not reflect changes to the database until the cache interval has expired. In some cases, this is acceptable; in other cases, we need to have changes to the database reflected immediately.
To handle scenarios that require the return of real-time recordsets, we have some options. We can choose not to use query caching, to use short cache time spans if we know when or how frequently updates will occur, or to force updates of the cached queries. In Listing 24.14, we have a query that returns a product list. The cache time span is set for 12 hours because updates are fairly infrequent.
Listing 24.14 Cached ProductList Query
<cfquery name="ProductList" datasource="#DSN#" cachedWithin=#CreateTimeSpan(0,12,0,0)#> SELECT ProductID, ProductName, ProductDescription, ProductPricePerUnit FROM Product WHERE CategoryID = 10 </cfquery>
This is a query that we want to cache, but when products are updated or added, we want the change to be reflected immediately. If we add a new product or change the price of a product, we do not want to run the risk of the change not being reflected for up to 12 hours. To force updates of the cached query, we can have the queries that effect changes followed by a CFQUERY that contains a negative cachedWithin value.
Listing 24.15 Forcing an Update of a Cached Query
<cfquery name="UpdateProduct" datasource="#DSN#"> UPDATE Product SET ProductPricePerUnit = '#newPrice#' WHERE ProductID = #ProductID# </cfquery> <cfquery name="ProductList" datasource="#DSN#" cachedWithin=#CreateTimeSpan(0,0,0,-1)#> SELECT ProductID, ProductName, ProductDescription, ProductPricePerUnit FROM Product WHERE CategoryID = 10 </cfquery>
There is one other way to force the update of cached queries. ColdFusion 5 introduced the CFOBJECTCACHE tag. Unlike our example in Listing 24.15, CFOBJECTCACHE tag flushes the entire query cache. If you use this tag, exercise caution, particularly in a shared hosting environment. It uses the following syntax:
There is another way to enhance performance that is related to SQL queries. When you create a datasource, one of the options is Maintain Database Connections. When this option is enabled, ColdFusion keeps the connection open after its first connection to the database. This means that ColdFusion is not constantly opening and closing database connections. This concept is outside the scope of this discussion, but is nonetheless important. If you do not fully understand what ColdFusion does when it encounters a SQL statement or how it handles database connections, you should read Macromedia TechNote 22128, "How Are Database Connections Handled in ColdFusion." It is available at www.macromedia.com/v1/handlers/index.cfm?ID=22128. Another excellent article is "ColdFusion Timeouts and Unresponsive Requests," which is available at www.macromedia.com/v1/Handlers/index.cfm?ID=21641&Method=Full.