MACROMEDIA ColdFusion MX, Develop Manual

Page: 281 / 860

Optimizing ColdFusion applications 281
Optimizing database use
Two important ColdFusion MX tools for optimizing your use of databases are the
cfstoredproc
tag and the
cfquery
tag
cachedWithin
attribute.
Note: 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.
Using stored procedures
The
cfstoredproc
tag lets ColdFusion MX 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
cfproresult
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" dbVarName = @param1>
<cflock scope="Application"
type="Exclusive"
timeout=30>
<cfset Application.productCache =
productcache>
</cflock>
Inside an Exclusive
cflock
tag, saves the contents of
the local variable
ProductCache
in the Application
scope variable Application.productCache.
</cfif>
Ends the code that executes only if the
Application.productCache variable does not exist.
<cflock scope="application" timeout="20"
type="readonly">
<cfoutput>#Application.ProductCache#<
/cfoutput>
</cflock>
Inside a
cflock
tag, displays the contents of the
Application.productCache variable.
Code Description