> My understanding is the query plan can be cached when only the VALUES change.
Let's be clear here- a DBMS will cache ALL execution plans. The real question really is whether or not the cached plan will ever be used or if it will sit around and gather dust until it is evicted from cache. (Or worse yet, fill the cache up so other plans are evicted) Creating a cfquery block with cfif statements that will produce one of 5 different blocks of SQL is no different as far as your database is concerned than creating 5 separate cfqueries. Assuming the code is run all 5 possible ways, your database should cache all 5 plans and will use the appropriate one assuming it is still laying around the cache the next time it is called. The benefit to using TSQL/PLSQL to account for your different scenarios (and cfqueryparam to generalize your parameters) is you have fewer unique SQL statements being sent from the app, and a more likely chance of the database cache keeping them all around and reusing them. The catch to the above sentence that is that not all execution plans are created equal and just because the DB has a cached plan from the last time it ran doesn't necessarily mean that it is the best plan for your current set of inputs. Whether or not you need to worry about most of this depends on how big your app is and how many unique SQL statements are being sent to your DB. Check out your DB server's stats on cache hit ratios if you're worried. ~Brad ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329566 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
