Well from what I read today it seems like the performance is improved for reused prepared statements, which may not be how cfqueryparam is implemented. Prepared statements are stored and reused by name, passing in the values for the parameters.
So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Any experts out there that can enlighten us? On Thu, Apr 16, 2009 at 12:33 PM, Dominic Watson < watson.domi...@googlemail.com> wrote: > > Just a little thing to add here is that I believe you should > parametize all values in your query, whether user generated or > constant values or whatever. This is not for security but performance. > So: > > SELECT fu > FROM bar > WHERE barId = <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#" /> > AND live = 1 > > Should be: > > SELECT fu > FROM bar > WHERE barId = <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#" /> > AND live = <cfqueryparam cf_sql_type="cf_sql_bit" value="1" /> > > The reason (and someone please correct me if I am wrong) is that > prepared statements can perform better because the db engine caches > their execution plan much in the same way as for stored procedures. By > parametizing and supplying the type of each value, you help this > process. Indeed I suspect that I have read somewhere that it will not > cache at all if it finds values that are not parametized. > > This is my rough understanding of it; please somebody who knows more > clarify or correct the point (I do know that db performance always > goes by the rule: "it depends" though). > > Dominic > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:321689 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
