Everything but your guess is correct ;) Dominic
2009/4/16 David McGuigan <davidmcgui...@gmail.com>: > > 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:321690 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
