Improving Query Performance with Caching

Nearly all ColdFusion applications have a database at their heart, and most ColdFusion templates contain at least one <cfquery> or other database interaction. In fact, depending on the type of application you are building, your ColdFusion templates might be solely about getting information in and out of a database. In such a situation, ColdFusion is basically behaving as database middleware, sitting between your database and your Web server.

Because database access is such an integral part of ColdFusion development, the server provides a number of features to the performance of your database queries. This section helps you understand which options are available and how to make the most of them.

In particular, we will discuss the following:

• Query caching, which cuts down on the amount of interaction between your database and ColdFusion. This can improve performance dramatically.

• Helping ColdFusion deal with larger query results via the blockfactor attribute.

NOTE

We once thought of ColdFusion as simply a database middleware application. In fact, very early versions of ColdFusion were so database-centric that what we now call CFML was known as DBML, and tags such as <cfoutput> and <cfif> were known as <dboutput> and <dbif>. With the addition of more services such as email, HTTP, LDAP, graphing, file manipulation, Flash, and Web Services integration, ColdFusion has expanded and matured into something much more interesting: a Web application server.

Understanding Query Caching

To improve performance, ColdFusion provides a wonderful feature called query caching. Basically, query caching allows ColdFusion to keep frequently used query results in its internal memory, rather than retrieving the results from the database over and over again.

You tell ColdFusion to cache a query by adding a cachedWithin or cachedAfter attribute to the <cfquery> tag. If one of your templates is visited often and contains a query that won't return different results each time it runs, you can usually give the page an instant performance boost by simply using one of these two special attributes. Table 25.1 explains what each of the attributes does.

Table 25.1. <cfquery> Attributes Relevant for Query Caching

ATTRIBUTE	PURPOSE
cachedWithin	Optional. Tells ColdFusion to cache the query results for a period of time, which you can specify in days, hours, minutes, or seconds. You specify the time period using the createTimeSpan() function.
cachedAfter	Optional. Tells ColdFusion to cache the query results based on a particular date and time. This attribute is generally less useful in real-world applications than cachedWithin. If you know that your database will be updated at a certain moment in time, perhaps after some type of external batch process, you can specify that date and time (as a ColdFusion date value) here.

Query caching is really easy to use. Say you use the following query in one of your ColdFusion templates:

 <cfquery name="GetFilms" datasource="ows">  SELECT FilmID, MovieTitle FROM Films </cfquery> 

Assuming that the data in the Films table doesn't change very often, it would probably be sufficient to only query the database occasionally, rather than with every page request. For instance, you might decide that the database really only needs to be checked for new or changed data every 15 minutes. Within each 15-minute period, the data from a previous query can just be reused. To get this effect, simply add a cachedWithin attribute that uses createTimeSpan() to specify a 15-minute interval, like this:

 <cfquery name="getFilms" datasource="ows"   cachedWithin="#createTimeSpan(0,0,15,0)#">  SELECT FilmID, MovieTitle FROM Films </CFQUERY> 

See Appendix C, "ColdFusion Function Reference," for information about the createTimeSpan() function.

That's all you have to do. The first time the query runs, ColdFusion interacts with the database normally and retrieves the film records. But instead of discarding the records when the page request is finishedas it would do normallyColdFusion stores the query results in the server's RAM. The next time the template is visited, ColdFusion uses the records in its memory instead of contacting the database again. It continues to do so for 15 minutes after the first query ran (or until the ColdFusion server is restarted). The next time the template is visited, the original records are flushed from the server's RAM and replaced with new records, retrieved afresh from the database.

There's more. Queries aren't cached on a per-page basis. They are cached on a server-wide basis. If two <cfquery> tags on two different pages specify exactly the same SQL code, datasource, name, dbtype, username, and password, they will share the same cache. That is, the first time either page is accessed, the database is contacted and the records are retrieved. Then, for the next 15 minutes (or whatever interval you specify), a visit to either page will use the cached copy of the query results.

NOTE

The SQL statements in the two <cfquery> tags must be exactly the same, including any white space such as tabs, indenting, and spaces. If they aren't the same, the two queries will be cached independently.

Clearly, if a query is at all time-consuming, the performance benefits can be tremendous. Every template that uses the cached query will be sped up. Plus, if the database and ColdFusion are on different machines, using query caching will likely cut down dramatically on network traffic. This tends to improve performance as well, depending on how your local network is configured.

NOTE

A possible disadvantage to caching a query is that changes to the actual data in the database won't show up in the cached version of the query, because the database isn't actually being contacted. Any new records (or updates or deletes) will show up only after the cache interval has expired. For details and solutions, see "Refreshing a Cached Query Programmatically," later in this chapter.

Using Cached Queries

One obvious situation in which ColdFusion's query caching feature can be of great benefit is when you're building a Next N type of record-browsing interface, such as the one presented in Chapter 24, "Improving the User Experience."

Listing 25.1 takes the NextN4.cfm template from Listing 24.7 of Chapter 24 and adds a cachedWithin attribute to the <cfquery> at the top of the template. Now ColdFusion doesn't need to keep rerunning the query as the user browses through the pages of records.

Listing 25.1. NextNCached.cfmAdding the cachedWithin Attribute to Speed Up Record Browsing

 <!---   Filename: NextNCached.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Next N record-navigation interface  Please Note Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks.cfm ---> <!--- Maintain ExpenseReport filtering variables at session level ---> <cfparam name="SESSION.expenseReport.userFilter" type="string" default=""> <cfparam name="SESSION.expenseReport.dateFrom" type="string" default=""> <cfparam name="SESSION.expenseReport.dateThru" type="string" default=""> <!--- If the user is submitting the "filter" form, ---> <!--- we'll make their submission be the filter for rest of session ---> <cfif isDefined("FORM.userFilter")>  <cfset SESSION.expenseReport.userFilter = FORM.userFilter>  <cfset SESSION.expenseReport.dateFrom = FORM.dateFrom>  <cfset SESSION.expenseReport.dateThru = FORM.dateThru> </cfif> <!--- Retrieve expense records from database ---> <cfquery name="getExp" datasource="#REQUEST.dataSource#"  cachedWithin="#createTimeSpan(0,0,15,0)#">  SELECT   f.FilmID, f.MovieTitle,   e.Description, e.ExpenseAmount, e.ExpenseDate  FROM   Expenses e INNER JOIN Films f  ON e.FilmID = f.FilmID  WHERE   0=0   <!--- If the user provided a filter string, --->   <!--- show only matching films and/or expenses --->  <cfif SESSION.expenseReport.userFilter is not "">  AND (f.MovieTitle LIKE '%#SESSION.expenseReport.userFilter#%' OR  e.Description LIKE '%#SESSION.expenseReport.userFilter#%')  </cfif>   <!--- Also filter on From date, if provided --->  <cfif isDate(SESSION.expenseReport.dateFrom)>  AND e.ExpenseDate >= #createODBCDate(SESSION.expenseReport.dateFrom)#  </cfif>  <!--- Also filter on Through date, if provided --->  <cfif isDate(SESSION.expenseReport.dateThru)>  AND e.ExpenseDate <= #createODBCDate(SESSION.expenseReport.dateThru)#  </cfif>  ORDER BY   e.ExpenseDate DESC </cfquery> <!--- Number of rows to display per Next/Back page ---> <cfset rowsPerPage = 10> <!--- What row to start at? Assume first by default ---> <cfparam name="URL.startRow" default="1" type="numeric"> <!--- Allow for Show All parameter in the URL ---> <cfparam name="URL.showAll" type="boolean" default="No"> <!--- We know the total number of rows from query ---> <cfset totalRows = getExp.recordCount> <!--- Show all on page if ShowAll passed in URL ---> <cfif URL.showAll>  <cfset rowsPerPage = totalRows> </cfif> <!--- Last row is 10 rows past the starting row, or ---> <!--- total number of query rows, whichever is less ---> <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)> <!--- Next button goes to 1 past current end row ---> <cfset startRowNext = endRow + 1> <!--- Back button goes back N rows from start row ---> <cfset startRowBack = URL.startRow - rowsPerPage> <!--- Page Title ---> <html> <head><title>Expense Browser</title></head> <body> <cfoutput><h2>#REQUEST.companyName# Expense Report</h2></cfoutput> <!--- Simple style sheet for formatting ---> <style>  FORM {font-family:sans-serif;font-size:smaller;}  TH {font-family:sans-serif;font-size:smaller;  background:navy;color:white}  TD {font-family:sans-serif;font-size:smaller}  TD.DataA {background:silver;color:black}  TD.DataB {background:lightgrey;color:black} </style> <!--- Simple form to allow user to filter results ---> <cfform action="#CGI.script_name#" method="POST">  <!--- Filter string --->  <b>Filter:</b>  <cfinput  type="text"  name="userFilter"  value="#SESSION.expenseReport.userFilter#"  size="15">    <!--- From date --->   &nbsp;  <b>Dates:</b> from  <cfinput  type="text"  name="dateFrom"  value="#SESSION.expenseReport.dateFrom#"  size="9"  validate="date"  message="Please enter a valid date, or leave it blank.">    <!--- Through date --->  through  <cfinput  type="text"  name="dateThru"  value="#SESSION.expenseReport.dateThru#"  size="9"  validate="date"  message="Please enter a valid date, or leave it blank.">     <!--- Submit button to activate/change/clear filter --->   <input   type="Submit"  value="Apply">  </cfform> <table width="600" border="0" cellspacing="0" cellpadding="1">  <!--- Row at top of table, above column headers --->  <tr>  <td width="500" colspan="3">  <!--- Message about which rows are being displayed --->  <cfoutput>  Displaying <b>#URL.startRow#</b> to <b>#endRow#</b>  of <b>#totalRows#</b> Records<br>  </cfoutput>  </td>  <td width="100" align="right">  <cfif not URL.showAll>  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </cfif>  </td>  </tr>  <!--- Row for column headers --->  <tr>  <th width="100">Date</th>  <th width="250">Film</th>  <th width="150">Expense</th>  <th width="100">Amount</th>  </tr>    <!--- For each query row that should be shown now --->  <cfloop query="getExp" startRow="#URL.startRow#" endrow="#endRow#">  <!--- Use class "DataA" or "DataB" for alternate rows --->  <cfset class = iif(getExp.currentRow mod 2 eq 0, "'DataA'", "'DataB'")>    <cfoutput>  <tr valign="baseline">  <td  width="100">#lsDateFormat(expenseDate)#</td>  <td  width="250">#movieTitle#</td>  <td  width="150"><i>#description#</i></td>  <td  width="100">#lsCurrencyFormat(expenseAmount)#</td>  </tr>  </cfoutput>  </cfloop>    <!--- Row at bottom of table, after rows of data --->  <tr>  <td width="500" colspan="3">  <cfif not URL.showAll and totalRows gt rowsPerPage>  <!--- Shortcut links for "Pages" of search results --->  Page <cfinclude template="NextNIncludePageLinks.cfm">  <!--- Show All link --->  <cfoutput>  <a href="#CGI.script_name#?&showAll=Yes">Show All</a>  </cfoutput>  </cfif>   </td>  <td width="100" align="right">  <cfif NOT URL.showAll>  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </cfif>  </td>  </tr> </table> </body> </html>  

If you want, you can watch which queries ColdFusion is actually caching by turning on the Database Activity option in the Debugging Settings page of the ColdFusion Administrator. Whenever a query is returned from the cache, the execution time will be reported as 0ms, accompanied by the words Cached Query, as shown in Figure 25.1. When the cache timeout expires, you will see the execution time reappear, in milliseconds, as it does normally.

Figure 25.1. Cached queries are fetched directly from ColdFusion's internal memory, which can greatly improve performance.

NOTE

When using query caching, you can't use the <cfqueryparam> tag in your SQL statement.

Refreshing Cached Queries Programmatically

Query caching is most often used for queries that don't change often over time, or in situations where it is acceptable for your application to show information that might be slightly out of date. However, you might run into situations in which you want a query cached for several hours at a time (because the underlying data hardly ever changes), but where it is very important for any changes that do get made to the database to be reflected right away.

Flushing A Specific Cached Query after an Update

ColdFusion doesn't provide a specific attribute for flushing a particular cached query, but you can achieve the same effect by including a <cfquery> tag with a negative cachedWithin value right after a relevant change is made to the database. This will force ColdFusion to contact the database and fetch the updated records. From that point on, the updated version of the query results will be what is shared with other pages that use the same query.

NOTE

Of course, this technique is not effective if the database is being updated via some application other than ColdFusion. Your Cold Fusion application needs to be aware of when to discard a cached version of a query.

For instance, let's say you are using the following cached query in your code:

 <cfquery name="getFilms" datasource="ows"   cachedWithin="#createTimeSpan(0,3,0,0)#">  SELECT * FROM Films </cfquery> 

Left to its own devices, this query's cache will only be refreshed every three hours. Now say that some other page updates one of the film records, perhaps using a <cfupdate> tag, like so:

 <cfupdate datasource="ows" tablename="Films"> 

Again, left to its own devices, the SELECT query will continue to show the cached records until the three-hour timeout expires. Only then will the changes that the <cfupdate> made be fetched from the database. However, you could force the updated records into the cache by placing the following query right after the <cfupdate>:

 <cfquery name="getFilms" datasource="ows"  cachedWithin="#CreateTimeSpan(0,0,0,-1)#">  SELECT * FROM Films </cfquery> 

Now, when the first SELECT query is next executed, it will read the updated records from the cache. Your application will always show the most current version of the records, even though it is usually reading the records from the query cache.

NOTE

The SQL statements in the two <cfquery> tags (the one that uses the cachedWithin of three hours and the one that uses the negative cachedWithin value) must be exactly the same, including indenting and other white space. The name and datasource attributes must also be identical, as well as anydbtype, user-name, and password attributes you might be providing. If not, ColdFusion will consider the queries separate for caching purposes, which means that the second query won't have the desired effect of refreshing the first.

Flushing All Cached Queries

As you just saw, you can use a negative value for a specific query's cachedWithin attribute to make sure a particular query gets removed from the query cache. This method is simple and straightforward, but you may also find that there are situations in which you would like to discard all cached query records. One way to do this is to simply restart the ColdFusion application server.

You can also refresh all cached queries programmatically, using the <cfobjectcache> tag. At this time, <cfobjectcache> takes one attribute, action, which must always be set to Clear. When ColdFusion encounters this tag in your code, all cached queries are discarded. The next time a <cfquery> tag is encountered for the first time, it will re-contact the database and retrieve the current data from your tables.

Here is how the tag would look in your code:

 <!--- Discard all cached queries ---> <cfobjectcache  action="Clear"> 

NOTE

This tag was present but undocumented in previous versions of ColdFusion. As of ColdFusion MX, it is a documented and supported part of the product.

Limiting the Number of Cached Queries

To ensure that your cached queries don't take up crippling amounts of the server's RAM, Cold Fusion imposes a server-wide limit on the number of queries that can be cached at any given time. By default, the limit is set to 100 cached queries. If a new <cfquery> tag that uses cachedWithin or cachedAfter is encountered after 100 queries are already in the cache, the oldest query is dropped from the cache and replaced with the new query.

NOTE

You can increase this limit by editing the Maximum Number of Cached Queries field in the Caching page of the ColdFusion Administrator. Keep in mind that the final SQL code determines how a query is cached. If you use a ColdFusion variable in the SQL portion of a <cfquery> tag, and the query is run with 10 different variable values during a given period, that will count as 10 queries toward the limit of 100. See Chapter 29 for details about using the ColdFusion Administrator.

Controlling How Many Records Are Fetched at Once

Normally, ColdFusion retrieves each record from your database individually. That said, if you know a query will return more than a few records, you can speed up ColdFusion a bit by giving it a hint about how many records are likely to be returned. To do so, provide a blockFactor attribute in your <cfquery> tags. In using blockFactor, make a reasonable guess as to how many records the query might return.

Don't provide a blockFactor value that is more than the number of records the query returns. If you do, your database driver will tell ColdFusion that the specified blockFactor is invalid, and ColdFusion will try againthis time repeatedly subtracting 1 from the value you supplied until blockFactor no longer exceeds the total number of records. This could slow down your query. Unfortunately, ColdFusion can't determine the appropriate blockFactor automatically.

For instance, if you know that the Films table will contain 25 or more records for the foreseeable future, you should provide a blockFactor value of 25, like this:

 <cfquery name="getFilms" datasource="ows" blockFactor="25">  SELECT * FROM Films </cfquery> 

The larger the number of records involved, the more effect blockFactor is likely to have on overall query performance. Don't obsess about getting blockFactor exactly right. Just think of it as a way to let ColdFusion know whether to expect a large number of records or just one or two. At the very least, consider providing a blockFactor="100" attribute for all queries that will return hundreds or thousands of records.

NOTE

If you are using stored procedures, it's worth noting that the <cfstoredproc> tag also supports the blockFactor attribute. See Chapter 31, "Working with Stored Procedures," for details.

NOTE

Currently, the maximum value that blockFactor allows is 100. If a query might return hundreds or thousands of records, you should still go ahead and set blockFactor="100". Because ColdFusion will be retrieving the records in 100-record chunks, this can often improve performance rather dramatically.