Adam, 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.
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. -Mk -----Original Message----- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 10:53 AM To: CF-Talk Subject: Re: Database performance - Java Q > http://blog.mxconsulting.com/mxc-blog/archives/entries/B17FBDFD-EBB8-DFFC-87 > E24B10A97A0BA2.shtml Folks, the article mentioned is *very* misleading, and it could lead to some bad decisions: >From the article: "Since each of the sprocs searched exactly one column, there is no need to recompile." This is utterly wrong, and is the most common misconception regarding stored procedures and execution plans. It doesn't matter if you search on the same column each time -- its the *value(s)* of the parameter(s) you send to the stored procedure that determine row selectivity, and that is one of the most critical elements of choosing the correct execution plan. I posted a message to this list about this subject a few weeks back. Here's a copy of it: -------- Let's say you create a stored procedure on SQL Server 2000 that takes a parameter. The first time this sp is executed, the database will settle on a lost-cost execution plan, compile it, and then reuse that plan every other time the sp is executed (until SQL2K is cycled or some directive demands otherwise). But let's say the parameter you first used was of such low selectivity that the execution plan used a table scan instead of engaging an index to do its job. Now, if you call the same sp using a parameter of high selectivity -- one that would engage an index for efficiency -- your performance will be less than if you had submitted your query as a simple batch using CFQUERY, because the sp uses the same execution plan it did for the first run, meaning it performs a full table scan to match only a few rows. You can specify a stored procedure to always recompile its execution plan by adding WITH RECOMPILE to its declaration, or you can specify a stored procedure to selectively recompile by calling it using WITH RECOMPILE, though there doesn't appear to be a direct mechanism for specifying the latter method from ColdFusion. Judging whether or not to recompile execution plans should be carefully considered based on IO statistics you gather during testing and through an understanding of how the values of parameters and indexes affect selectivity. ------- I hope this clears things up a bit. 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:183881 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
