Something of note... If you are using cfqueryparam all of the variables
need to be bound. Leaving any variable "hanging out there" will not allow
you to take advantage of the execution plan - even if it's a constant.
This query
<cfquery>
SELECT col1,col2
FROM users
WHERE active = 1
AND userName = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="bob">
</cfquery>
Will not pop the exec plan cache because the server will need to evaluate
the "1" after active to type it as an int. To make it work it would need to
be written as:
<cfquery>
SELECT col1,col2
FROM users
WHERE active = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="1"/>
AND userName = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="bob">
</cfquery>
Of course the top query is quite safe from injection.. It just has no chance
of hitting the cache.
In addition, server configuration issues on the SQL server will determine
how effective it is at hitting the cache. By default it does a pretty good
job, but it can need adjusting... Say when there are a few hundred databases
for example.
-Mark
Mark A. Kruger, CFG, MCSE
(402) 408-3733 ext 105
www.cfwebtools.com
www.coldfusionmuse.com
www.necfug.com
-----Original Message-----
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 30, 2008 11:28 AM
To: cf-talk
Subject: Re: cfqueryparam vs cfstoredproc?
Interesting...
I thought the same thing until I ran these tests. I analyzed the results
with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion,
and watched them execute through SQL Profiler, all of them showed better
execution times when I removed the CFQUERYPARAMs. Now, this was on a limited
subset of my queries. I would still argue that using it is far better than
not. Most of the Queries I have used it in I did see a performance
improvement in.
=]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314620
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
