> I'm sorry... but the way you described it below is exactly the same message > I got from the article - that the value of "ordery by" was deterimining the > execution plan and subsequently slowing down the stored procedure when other > values were specified.
To clarify a little, the fact that he was using the value of the parameter to choose one column to search rather than another was not the point. Notice the last sentence of this quote from the article: --- "I could have created a separate sproc for each search method, and then decide which one to execute within the CASE block. That way, the query plan associated with the sub-sprocs remains in the cache, where SQL can take advantage of them. Since each of the sprocs searched exactly one column, there is no need to recompile." --- He's basically saying that the value(s) of the arguments used to search against that "one column" are not important, and this is my point of contention because those values are absolutely critical to the process of choosing an execution plan. In other words, he's stopping so short of the real issue that it's quite misleading. > More to the point I think perhaps your comments are misleading. If a cached > execution plan is "select col1, col2 where col1 = 'ABC', then you are > correct in assuming that it will do no good unless 'ABC' is passed in again > and again. But if the cached plan is "select col1, col2 where col1 = > @boundCharacterVar1, then you will indeed get a cached hit by binding 'abc' > or '123' or whatever. That's the beauty of databinding. I believe you have a misconception of just what is and is not part of an execution plan, the difference between a bind variable and data binding, and how bind variables do and do not affect the selection of an execution plan. I promise that I'm not trying to give you a hard time, Mark. I've been thinking about writing an article regarding this topic because it is so misunderstood. Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive Training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183907 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
