Comparing two stored procedures

[TysonLPrice]

Hi everyone,

First I like to say thanks to all the contributors of this forum. I come here every morning to read the posts and feel like I've learned a lot from all of you.

I'm trying to use some of the things I've learned here to optimize existing stored procedures I inherited. I took a bunch of joins and cursors out of one and need to compare the performance between the two. I have them copied into Query Analyzer right now. I'm comparing them like this:

Get the start time:

declare @testtime as varchar(30)
set @Testtime = convert(varchar(30), getdate(), 126)
print @testtime

Procedure processing:

Get the end time:

set @Testtime = convert(varchar(30), getdate(), 126)
print @testtime

The format of @Testtime is 2004-10-13T07:22:59.623

Assuming the processing results are the same is this a valid test? It looks like I took a second off so far. Small potatoes I know but I'm working with a subset of production.

Are there better ways to compare results?

Thanks!

 

[hmckillop]

i use a similiar method, only problems I would have is, if you are testing on a remote server you need to ensure that network traffic and such arent effecting results, or that other work on the server (locking etc) isnt effecting results.
Other things I would also do though are :
run profiler and include columns such as reads, writes and duration.
Ensure you look at the execution plan etc.

I am sure you have thought of these but just in case.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

And remebr that the more records affected the more time savings there will be. So if you used a small subset to trun this against, the savings on your real data should be larger.

Questions about posting. See faq183-874