Maybe I'm confused a bit on what we are talking about then. My assumption was that it _is_ the process of compiling the SQL that generates the plan based on how the DB expects the code to run, the sizing of its temp tables, and the input being used, etc. >From my research in the past on MS SQL Server, the DB will stop even in the middle of a proc and recompile because of any number of factors. For instance, a declaration of a temp table inside of a conditional statement will cause a recompile. Your original statement was that the execution plan of a piece of SQL code would differ based on the input to it. Therefore, my statement that drastically changing the input to a prepared statement would require a recompile in order to generate a more acceptable plan.
Are you saying that compilation and plan generation are two separate activities, and furthermore that a new plan can be determined for a statement without recompiling? Please explain. ~Brad -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Friday, August 10, 2007 4:08 PM To: CF-Talk Subject: RE: Is cfqueryparam worth it? > Still, in that case, the DB will simply perform a recompile > to find a more suitable execution plan-- a step which would > have had been performed anyway had there not have been a > cached plan. So the worst case scenario seems to be you > would break even. Doesn't seem like you can go wrong with that. Uh, no, the database will not simply perform a recompile unless there is no plan in the cache, or unless you explicitly tell it to recompile, or there's some other system flag that would force a recompile. The database can't know ahead of time whether there would be a more suitable plan without actually building that plan (which, of course, would defeat the purpose of caching the execution plan in the first place). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286001 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
