> 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:286004 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
