Database access (and waiting for databases) is the single biggest bottleneck in ColdFusion applications. As such, ColdFusion developers invest (or should invest) a significant amount of time into improving database access time (and even eliminating it when possible). There are lots of things that can negatively impact database performance:
Stored procedures, relational databases, and SQL are covered in the previous four chapters (Chapters 44-47). Even if all these issues were to be addressed properly, they'd still be a potential database bottleneck in that most Web-based applications have to make frequent databases requestsoften for the same data over and over. This being the case, an important part of optimizing database performance is preventing unnecessary database access by saving query results for later use. ColdFusion provides two very different ways to accomplish this, each designed for very different situations:
Variable-Based Query CachingSome database queries change infrequently (or never), and are thus perfect candidates for caching. Examples of these types of queries are:
Variable-based query caching takes advantage of ColdFusion's support for persistent data by using the scopes designed for just that purpose. Three scopes are supported:
For an explanation of how to use these persistent scopes, see Chapter 10, "APPLICATION and SERVER Variables and SERVER Variables," and Chapter 11, "Session State Management." CAUTION As a rule, use of the SERVER scope should be avoided. Most variable-based query caching belongs in the APPLICATION scope unless it is user-specific (in which case it belongs in the SESSION scope). The SERVER scope is shared by all applications and is therefore far more susceptible to the introduction of bugs or corruption by other developers or applications. Storing a variable in the APPLICATION scope (for example) is as simple as prefixing the variable name with the scope specifier, as follows: <cfset APPLICATION.dsn="my_data_source"> Caching queries into the APPLICATION scope is just as simple. The following example reads the list of U.S. states into a query within the APPLICATION scope (using the previously assigned APPLICATION variable as the datasource): <cfquery datasource="#APPLICATION.dsn#" name="APPLICATION.states"> SELECT state_name, state_abbrev FROM states ORDER BY state_name </cfquery> This query now resides within the APPLICATION scope and will persist for as long as APPLICATION variables are configured to persist. The query can be used like any other query, and must be referred to by the fully qualified name (scope plus name). The following example populates a drop-down list box with the list of states: <select name="state"> <cfoutput query="APPLICATION.states"> <option >#state_name#</option> </cfoutput> </select> Of course, if the query is in the APPLICATION scope, then the majority of times it is used it will not need to be retrieved from the database. As such, the code that creates the query should always first check whether the query exists. The following code snippet demonstrates this process: <cfif NOT IsDefined("APPLICATION.states")> <cfquery datasource="#APPLICATION.dsn#" name="APPLICATION.states"> SELECT state_name, state_abbrev FROM states ORDER BY state_name </cfquery> </cfif> Using this code, the list of U.S. states can be read from the database just once (or as often as needed), and then used and reused without anyone ever having to access the database again. This can dramatically reduce redundant database access, which in turn can dramatically improve application performance. TIP The Application files are the perfect place for initializing APPLICATION scope data. See Chapter 6, "The Application Framework," for more information. Of course, the other scopes can be used just as easily; just specify the appropriate prefix, and ColdFusion does the rest. Variable-based query caching is not suited for queries that need to persist for short periods. Nor is it well suited for dynamic queries (for example, queries that are driven by form fields). Query Result CachingVariable-based query caching is ideal for queries that change infrequently, are not highly dynamic, and are of use across users or parts of applications. For all other queries, ColdFusion features another form of caching: query result caching. This form of query caching is ideally suited for:
Query result caching is specified within the <CFQUERY> tag by using one of two optional (and mutually exclusive) attributes:
The following example attempts to cache the results of a dynamic, form-driven SELECT statement: <cfquery datasource="dsn" name="product_search" cachedwithin="#CreateTimeSpan(0,0,10,0)#"> SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_name LIKE '%#Trim(FORM.prod_name)#%' ORDER BY prod_name </cfquery> Here the cachedwithin attribute specifies a time span of 10 minutes (0 days, 0 hours, 10 minutes, and 0 seconds). If the query is cached, any further queries for the same data within that interval will use the cached data automatically. TIP Most developers find that cachedwithin is the more useful of the two cache attributes. cachedafter is of use only when data is being retrieved from databases that are updated at known regular times. But it is important to understand that cachedwithin (and cachedafter) are not instructions to cache data, they are requests. Data will be cached only if the administrator so allows, and if there is sufficient space in the cache. If the data can be cached, it will; if not, it won't. In fact, there is no way for developers even to know whether or not the query was cached. This is deliberate and by design, as this form of caching is designed to be as hands-off and transparent as possible. NOTE The number of queries that may be cached server-wide is specified in the ColdFusion Administrator. Using cached queries is no different from using any queriesjust refer to the query name. If a cached query can be used, it will; if not, it will not be used. The cached query will not be used if neither of the cache attributes is specified. But, if either of them is specified, and a cached query exists, ColdFusion will use it automatically. NOTE What makes a cached query unique is not the query name. In fact, a query can use a cached query even if the name is different, as long as it is the same query. So, what is it that makes a query unique? It's the combination of the following:
TIP If debugging output is enabled, ColdFusion will let you know that a cached query was used (in lieu of the standard execution time output). |