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.
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!
----- Original Message -----
From: "Adrian Lynch" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, August 26, 2004 9:44 AM
Subject: RE: stored procedures
> "That is where the benefit of SP's are, that once run all future runs are
> significantly faster."
>
> I think that this isn't always true. It depends what statements are within
> the SP. It's possible that an SP could produce various execution plans and
> the one that's cached might not be the best one for future calls. In the
> past I've seen (and been guilty of writing) SPs that do significantly
> different things depending on the incoming params. I did have some good
> links with more specific details but I can't remember/find them at the
> moment.
>
> Anyone care to elaborate?
>
> Ade
>
> -----Original Message-----
> From: Bill Grover [mailto:[EMAIL PROTECTED]
> Sent: 26 August 2004 13:53
> To: CF-Talk
> Subject: RE: stored procedures
>
>
> True, but coming up with the execution plan is what takes the longest and
> has the greatest impact on the server. So by having a SP once it is
> executed and that execution plan is cached they become very quick.
>
> For example, I have a SP that uses about 10 select statements to retrieve
> data from 4 different databases and about 15 different tables. The 1st
time
> it is run it takes about 10-15 seconds to return 2,100 rows. If I wait a
> few seconds and run it again it takes about 3 seconds to return the same
> rows. That is where the benefit of SP's are, that once run all future
runs
> are significantly faster.
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
