On Mon, Jan 19, 2009 at 5:37 PM, Adrian Lynch wrote: > Is it true that the SQL will perform better if you cfqp any static values > too? So: > > WHERE <cfqp value="1"> > > over: > > WHERE id = 1
I'm sure you can design scenario's where that will be faster (mainly where you are approaching the maximum number of prepared statements cached), but in general it will be slower for two reasons: 1. More work at runtime. Prepared statements reduce the amount of work in the time-critical query execution path by caching the query execution plan. When executed the database only has to fill out the parameters and execute the statement and doesn't have to parse and optimize the query. Filling out fewer variables is faster, both on the database side and on the CF side. (The CF side might actually be the dominant factor here.) 2. Less optimized execution plans. If you provide the value at compile time the optimizer has more information on how to optimize the query. This is especially relevant for queries where the optimal execution plan is dependent on the values of the variables. Examples of such queries are queries with a very skewed value distribution in a join / filter column or queries with inequality operators. For instance, if you have the predicate WHERE column < <cfqueryparam> for some values you will be selecting almost every row and want a heap scan, while for other values you will only select very few rows and want an index scan. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:318231 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
