Optimizing database use



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, to average the price of many products from a 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 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:

Code

Description

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

Runs the stored procedure foo_proc on the MY_SYBASE_TEST data source. Populates the cfstoredprocstatusCode variable with the status code returned by stored procedure.

 
<cfprocresult name = RS1> 
<cfprocresult name = RS3 resultSet = 3>

Gets two record sets from the stored procedure: the first and third result sets it returns.

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

Specifies two parameters for the stored procedure, an input parameter and an output parameter. Sets the input parameter to 1 and the ColdFusion variable that gets the output to FOO.

Ends the cfstoredproc tag body.

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

Displays the results of running the stored procedure:

  • The output parameter value,

  • The contents of the two columns in the first record set identified by the name and DATE_COL variables. You set the values of these variables elsewhere on the page.

  • The number of rows and the names of the columns in the first record set

  • The contents of the columns in the other record set identified by the col1, col2, and col3 variables.

  • The number of rows and the names of the columns in the record set.

  • The status value returned by the stored procedure.

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 significantly limits 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 up- to-date results.

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>