The only issue I have run into with CFQUERYPARAM is that is can degrade
performance on dynamic queries.
This is an inherent issue in what CFQUERYPARAM does, it essentially makes
your queries into stored procs, if you actually watch the traffic flow
through a MS SQL Server for example, you will see your app is actually
calling what appear to be stored procs.
The performance issue manifest when your query has dynamic bits, such as the
following example:
SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#Session.ClientCode#">
<cfif IsDefined("Form.FnameFilter")>
AND
Fname = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#Form.FnameFilter#">
</cfif>
<cfif IsDefined("Form.LnameFilter")>
AND
Lname = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#Form.LnameFilter#">
</cfif>
The first time CF processes this query, it creates some sort of memory based
or temporary stored proc for it based on the structure of the query as it
was run in this instance. It builds up an image of the query based on the
CFQUERYPARAMS used. Now if the next time the query is executed one or more
of the IF statements has a different result, thus including or excluding one
or more, then CF has to recompile the temporary stored proc. This can cause
a small performance loss.
In some performance tuning I did on an app earlier this year, I had 1 query
that was executed several thousand times in a long looping process. Nearly
every time it was hit it was different and forced a recompile. I removed all
of the CFQUERYPARAMs from it and it's average execution time went from 350ms
to 10ms.
Obviously, if this is a query that only executes occasionally, the
difference between 350ms and 10 ms is nothing to worry about, but over
millions of executions a day, it adds up.
=]
--
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:314609
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
