Eliminating Unnecessary Database Access


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:

  • Poor database and table design

  • Nonrelational tables

  • Incorrect data types used

  • Poorly written SQL

  • Lack of indexes

  • Not using stored procedures

  • … and much more

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 caching

  • Query result caching

Variable-Based Query Caching

Some database queries change infrequently (or never), and are thus perfect candidates for caching. Examples of these types of queries are:

  • Countries

  • U.S. states and Canadian provinces

  • Company information (such as addresses and phone numbers)

  • Product catalogs

  • Employee extensions and email addresses

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:

  • Data that needs to persist for a single user can be stored in the SESSION scope.

  • Data that needs to be available application-wide can be stored in the APPLICATION scope.

  • Data that needs to be available across all applications on a server can be stored in the SERVER scope.

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 Caching

Variable-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:

  • Search results

  • "Next N"style interfaces

  • User-specific queries

Query result caching is specified within the <CFQUERY> tag by using one of two optional (and mutually exclusive) attributes:

  • cachedwithin is used to cache data for a specified interval (relative time) which is specified as a time span (using the CreateTimeSpan() function).

  • cachedafter is used to cache data after a specific date (specified as absolute time) is reached.

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:

  • Datasource name

  • Datasource type (if specified)

  • SQL text (post and dynamic and programmatic processing)

  • Login information (if specified)


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).




Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net