August 19, 2009 at 9:00 am
I have written a set-based stored procedure to replace a stored procedure that uses a cursor (the cursor-based sproc is used currently in production). My boss wants me to prove that the set-based code has better performance than the cursor before I can deploy the change.
I know the basics of how to use SET STATISTICS IO and SET STATISTICS TIME, but the cursor loops through more than 1.3 million rows, and I seem to get results for every loop. When I ran it on my local machine, Management Studio crashed.
Is there a way to get the results for the stored procedure as a whole? Or another way to compare the performance of set-based code to cursor-based code.
Thanks,
Marianne
August 19, 2009 at 10:01 am
If you have a test system (other than you desktop preferably) you should be able to use Profiler to trace the execution of both procedures and use that to provide your boss with a comparision between to the two procedures. I'd check out Profiler in BOL (Books Online) for more information.
Off the top of my head I'm not sure what you would need to capture, I'd have to do some research myself.
August 19, 2009 at 10:11 am
Profiler with an SP:Completed event should do the trick. That'll show the total duration, reads, writes and CPU that the procedure did.
The sp:completed event is not one of the default ones, you'll find it under the Stored Procedure groupings. You can remove all the other events that are default, they're not of interest here.
Are you at all familiar with Profiler?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 10:17 am
If the time difference is extreme, you can simply execute both procs in Management Studio and look at the execution time at the bottom of the screen.
I've had ones where that was enough by itself to convince people that one version was better than another.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 10:37 am
Thanks so much for the quick responses. I am somewhat familiar with Profiler, so I will take a look at that. Right now, I am running both versions on our test machine using the code below to get the basic run times. My plan is to run each four times. My code ran consistently in 131 seconds. The cursor is still on its first run (at over 22 minutes so far). That might actually be enough.
--Clean cache & buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--Declare variables
Declare @Start datetime, @End datetime
Set nocount on
--Execute proc
set @Start = getdate()
exec dbo.etl_SalesLoad
set @End = getdate()
select 'sales',count(1) RecordCount, datediff(second, @Start, @End) DurationSecs, @Start StartTime, @End EndTime
from sales
August 19, 2009 at 11:47 am
Just over 2 minutes vs over 22 minutes should be good enough. Shouldn't need Profiler for this one. But it's still a good idea to get familiar with it. It'll come in useful a lot over time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 4:12 pm
just a quick update: I failed to ensure that the main table in my test environment had the same indexes as in production. :blush:
After creating the indexes (which did not affect the performance of my code), the cursor ran in about 7 minutes. Using Profiler (I had to use SQL:BatchCompleted to get CPU, Reads, and Writes), my set-based code showed the following improvements (% difference) over the cursor (an average from 3 executions each):
67.7% - Duration
33.8% - CPU
27.8% - Reads
My code actually has more writes, since I use a series of temp tables.
Thanks to everyone for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply