Poor database design and incorrect or inefficient use of the database are among the most common causes of inefficient applications. Consider the different methods that are available for using databases and information from databases when you design your application. For example, if you need to average the price of a number of products from an SQL query, it is more efficient to use SQL to get the average than to use a loop in ColdFusion.

Two important ColdFusion tools for optimizing your use of databases are the cfstoredproc tag and the cfquery tag cachedWithin attribute.

Using stored procedures

The cfstoredproc tag lets ColdFusion use stored procedures in your database management system. A stored procedure is a sequence of SQL statements that is assigned a name, compiled, and stored in the database system. Stored procedures can encapsulate programming logic in SQL statements, and database systems are optimized to execute stored procedures efficiently. As a result, stored procedures are faster than cfquery tags.

You use the cfprocparam tag to send parameters to the stored procedure, and the cfprocresult tag to get the record sets that the stored procedure returns.

The following example executes a Sybase stored procedure that returns three result sets, two of which the example uses. The stored procedure returns the status code and one output parameter, which the example displays.

<!--- cfstoredproc tag --->
<cfstoredproc procedure = "foo_proc" dataSource = "MY_SYBASE_TEST" 
    username = "sa" password = "" returnCode = "Yes">

    <!--- cfprocresult tags --->
    <cfprocresult name = RS1>
    <cfprocresult name = RS3 resultSet = 3>

    <!--- cfprocparam tags --->
    <cfprocparam type = "IN"
        CFSQLType = CF_SQL_INTEGER
            value = "1">
    <cfprocparam type = "OUT"    CFSQLType = CF_SQL_DATE
        variable = FOO>
<!--- Close the cfstoredproc tag. --->
</cfstoredproc>

<cfoutput>
    The output param value: '#foo#'<br>
</cfoutput>

<h3>The Results Information</h3>
<cfoutput query = RS1>
    #name#,#DATE_COL#<br>
</cfoutput>
<br>
<cfoutput>
    <hr>
    Record Count: #RS1.recordCount#<br>
    Columns: #RS1.columnList#<br>
    <hr>
</cfoutput> 

<cfoutput query = RS3>
    #col1#,#col2#,#col3#<br>
</cfoutput>
<br>
<cfoutput>
    <hr><br>
    Record Count: #RS3.recordCount#<br>
    Columns: #RS3.columnList#<br>
    <hr>

    The return code for the stored procedure is: '#cfstoredproc.statusCode#'<br>
</cfoutput>

Reviewing the code

The following table describes the code and its function:

For more information on creating stored procedures, see your database management software documentation. For more information on using the cfstoredproc tag, see the CFML Reference.

Using the cfquery tag cachedWithin attribute

The cfquery tag cachedWithin attribute tells ColdFusion to save the results of a database query for a specific period of time. This way, ColdFusion accesses the database on the first page request, and does not query the database on further requests until the specified time expires. Using the cachedWithin attribute can significantly limit the overhead of accessing databases that do not change rapidly.

This technique is useful if the database contents only change at specific, known times, or if the database does not change frequently and the purpose of the query does not require absolutely up- to-date results.

You must use the CreateTimeSpan function to specify the cachedWithin attribute value (in days, hours, minutes, seconds format). For example, the following code caches the results of getting the contents of the Employees table of the cfdocexamples data source for one hour.

<cfquery datasource="cfdocexamples" name="master"
    cachedWithin="#CreateTimeSpan(0,1,0,0)#">
    SELECT * FROM Employees
 </cfquery>