optimization, comparing execution plan

I have a sp that was taking longer than it should. I found a large
inefficiency in the code (a loop that executed the same expensive SQL
multiple times), and so I re-implemented a large portion of que code.
Because of this I'm having trouble comparing the 'efficiency' of the new sp
over the old one, because I'm not sure how to compare measurements from the
execution plan between the two functions. If I'm optimizing one sp, it's
easy to see the optimization is good when the cost numbers in the same code
paths start decreasing. But I don't think these numbers have any meaning
when comparing between two different functions with different execution
paths altogether.
So, how would you recommend I compare the two so that I can definitively say
the new sp is faster and more efficient than the old one?
Thanks.axis
To compare both sp
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Run First one
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Run second one
"axis" <none@.none.com> wrote in message
news:4253f751$1@.news.microsoft.com...
> I have a sp that was taking longer than it should. I found a large
> inefficiency in the code (a loop that executed the same expensive SQL
> multiple times), and so I re-implemented a large portion of que code.
> Because of this I'm having trouble comparing the 'efficiency' of the new
sp
> over the old one, because I'm not sure how to compare measurements from
the
> execution plan between the two functions. If I'm optimizing one sp, it's
> easy to see the optimization is good when the cost numbers in the same
code
> paths start decreasing. But I don't think these numbers have any meaning
> when comparing between two different functions with different execution
> paths altogether.
> So, how would you recommend I compare the two so that I can definitively
say
> the new sp is faster and more efficient than the old one?
> Thanks.
>|||Thanks. I had tried that and it comes back with 30 to 40 lines of time
measurements (it seems to measure each statement execution). Is there a way
to just get the sp execution time?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esjQGnrOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> axis
> To compare both sp
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run First one
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run second one
>
>
> "axis" <none@.none.com> wrote in message
> news:4253f751$1@.news.microsoft.com...
> sp
> the
> code
> say
>|||If I turn on TIME and execute one SP, would adding up all the "elapsed time
= #ms" messages I get give me a rough estimate of the sp's execution time?
"axis" <none@.none.com> wrote in message news:4253fb31@.news.microsoft.com...
> Thanks. I had tried that and it comes back with 30 to 40 lines of time
> measurements (it seems to measure each statement execution). Is there a
way
> to just get the sp execution time?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esjQGnrOFHA.1476@.TK2MSFTNGP09.phx.gbl...
new
from
it's
meaning
execution
definitively
>|||Uri,
Did you mean DBCC FREEPROCCACHE?
AMB
"Uri Dimant" wrote:

> axis
> To compare both sp
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run First one
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run second one
>
>
> "axis" <none@.none.com> wrote in message
> news:4253f751$1@.news.microsoft.com...
> sp
> the
> code
> say
>
>|||A quick way would be to compare the execution plan of the old and
optimized SP in one go. Highlight the two stored procedures and do
Ctrl-L, this should show you the relative query cost of one over the
other.
Aramid
On Wed, 6 Apr 2005 10:50:22 -0400, "axis" <none@.none.com> wrote:

>I have a sp that was taking longer than it should. I found a large
>inefficiency in the code (a loop that executed the same expensive SQL
>multiple times), and so I re-implemented a large portion of que code.
>Because of this I'm having trouble comparing the 'efficiency' of the new sp
>over the old one, because I'm not sure how to compare measurements from the
>execution plan between the two functions. If I'm optimizing one sp, it's
>easy to see the optimization is good when the cost numbers in the same code
>paths start decreasing. But I don't think these numbers have any meaning
>when comparing between two different functions with different execution
>paths altogether.
>So, how would you recommend I compare the two so that I can definitively sa
y
>the new sp is faster and more efficient than the old one?
>Thanks.
>|||On Wed, 6 Apr 2005 11:06:51 -0400, axis wrote:

>Thanks. I had tried that and it comes back with 30 to 40 lines of time
>measurements (it seems to measure each statement execution). Is there a way
>to just get the sp execution time?
Hi Axis,
Here's a method that I often use:
-- First, write dirty buffers to disk and clear the data cache,
-- so that measuring is not influenced by logical vs physical I/O
CHECKPOINT
DBCC DROPCLEANBUFFERS
-- Also clear out the procedure cache to get rid of old plans
DBCC FREEPROCCACHE
go
DECLARE @.started datetime, @.ended datetime
SET @.started = CURRENT_TIMESTAMP
/* Insert code to performance-test here */
SET @.ended = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.started, @.ended) AS "ms elapsed"
go
Perform your tests when noone else is using the system. Run the test a
couple of times and use the average execution time (even when noone is
using the server, there might be maintenance processes or OS overhead
kicking in from time to time).
If the code isn't very long-running, you might include a loop to execute
it 10 times before calculating elapsed time. The first one will run
slower (since data has to be read into cache), the next executions will
read data from cache, so that other efficiency aspects than just
physical I/O will influence the results.
Oh, before I forget - if the code to be tested includes queries that
return data to the front-end and the results are more than just a few
rows, than you should modify this to SELECT ... INTO #Temp FROM ...
This will route the output to a temp table instead of to the QA output
window. If you don't do this, you'll only measure how fast QA can
format, display and scroll the result set...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)