[thelist] tip owed

[Bruce Heerssen]

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