[thelist] tip owed
Bruce Heerssen
bheerssen at visualbridge.tv
Tue Dec 12 14:48:28 CST 2000
I'm sure I owe a tip or two. Here's one.
<tip type="database" author="Bruce Heerssen">
Help minimize the time database connections are held open by building your
dynamic queries as a string before opening a database connection. Then after
opening the db connection, pass that string as the query. For Instance, in
ColdFusion, it is common practice to build a query like so:
<cfquery name="getArticles" datasource="myDSN">
select * from articles
<cfif isDefined('form.keywords')>
where keywords like '#form.keywords#%'
<cfelseif isDefined('url.catid')>
where catid = #url.catid#
</cfif>
</cfquery>
The problem with building queries like this is that the connection is held open
while the conditional processing takes place. With a very complex query (the
example is kept simple for brevity), that could cause database locking errors
and timeouts under high load.
Here is a more database-friendly solution:
<cfset sql="select * from articles ">
<cfif isDefined('form.keywords')>
<cfset sql = sql & "where keywords like '#form.keywords#%'">
<cfelseif isDefined('url.catid')>
<cfset sql = sql & "where catid = #url.catid#">
</cfif>
<cfquery name="getArticles" datasource="myDSN">
#preserveSingleQuotes(sql)#
</cfquery>
* Note: preserveSingleQuotes() is required to properly format the query in
ColdFusion.
</tip>
Bruce Heerssen
Software Engineer
Visual Bridge, Inc.
http://www.iecommerce.net
(713) 350-8321 ext. 8358
More information about the thelist
mailing list