Ahh, I think I see what you are saying. To be more correct I should have worded your quote as "the _OPTIMAL_ execution plan of a piece of SQL code differs based on your input".
Given that the DB is likely to recompile the code if it senses that the current plan may not be optimal I assumed you were pointing out that there would be overhead in the generation of the new plan. It appears instead, you were pointing out that the code may used the cached plan which may not be optimal. Of course, I guess that could be a "draw back" of stored procs even. In other words, usage of a non-optimal plan is not directly the fault of cfqueryparam, but a side-effect of any manner of SQL execution allows for caching. ~Brad -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Friday, August 10, 2007 6:23 PM To: CF-Talk Subject: RE: Is cfqueryparam worth it? > 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. There are several factors that may cause the recompilation of the execution plan for a specific SQL batch. The SQL Server 2005 whitepaper I linked earlier describes these factors in detail: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx However, in general, cached execution plans will be reused even if they aren't optimal, which is all I was getting at. > Your original statement was that the execution plan of a > piece of SQL code would differ based on the input to it. No, my original statement was this: "On the other hand, the more complex the query, the more likely that an execution plan that's optimal for one set of values may not be optimal for another set!" The optimal execution plan for a specific SQL batch may differ based on the input to it. If there is an existing plan, it will generally be reused. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286005 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
