|
Home > Archive > MS SQL Server New Users > April 2005 > optimization, comparing execution plans
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
optimization, comparing execution plans
|
|
|
| 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 the code. At
this point the sp's are sufficiently different that I could consider them
different functions. Because of this I'm having trouble comparing the
'efficiency' of the new sp over the old one -- I'm not sure how to compare
measurements from the execution plan between the two different 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.
| |
|
| I've tried SET STATISTICS TIME ON by the way, but it returns a ton of time
measurements. Is there a way for it to only return the elapsed time for the
sp as a whole?
"axis" <none@none.com> wrote in message
news:4253fa93$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 the code. At
> this point the sp's are sufficiently different that I could consider them
> different functions. Because of this I'm having trouble comparing the
> 'efficiency' of the new sp over the old one -- I'm not sure how to compare
> measurements from the execution plan between the two different 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.
>
>
| |
| Cristian Lefter 2005-04-06, 8:02 pm |
| A simple method will be to execute them both in Query Analyzer and use
execution plan to see the cost for each procedure (relative to the batch).
Something like:
code:
declare @StartTime datetime
declare @EndTime1 datetime
declare @EndTime2 datetime
set @StartTime = getdate()
exec MyProcOld
set @EndTime1 = getdate()
exec myProcNew
set @EndTime2 = getdate()
select datediff(ms,@StartTi
me,@EndTime1) as [MyProcOld time]
,datediff(ms,@EndTim
e1,@EndTime2) as [MyProcNew time]
Cristian Lefter, SQL Server MVP
"axis" <none@none.com> wrote in message
news:4253fb6c$1@news
.microsoft.com...
> I've tried SET STATISTICS TIME ON by the way, but it returns a ton of time
> measurements. Is there a way for it to only return the elapsed time for
> the
> sp as a whole?
>
> "axis" <none@none.com> wrote in message
> news:4253fa93$1@news
.microsoft.com...
> the
> say
>
>
| |
|
| > 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?
Run them! make sure you dont upset any data with a transaction
Begin Transaction
exec some_proc
Rollback Transaction
Then you'll get real timings.
|
|
|
|
|