WAITFOR command

Question

Given the problem that a stored procedure on SQL Server 2005, which is looping through a cursor, must be run once an hour and it takes about 5 minutes to run, but it takes up a large chunk of processor time:

edit: I'd remove the cursor if I could, unfortunatly, I have to be doing a bunch of processing and running other stored procs/queries based on the row.

Can I use WAITFOR DELAY '0:0:0.1' before each fetch to act as SQL's version of .Net's Thread.Sleep? Thus allowing the other processes to complete faster at the cost of this procedure's execution time.

Or is there another solution I'm not seeing?

Thanks

Answer 1

I'm not sure if that would solve the problem. IMHO the performance problem with cursors is around the amount of memory you use to keep the dataset resident and loop through it, if you then add a waitfor inside the loop you're hogging resources for longer.

But I may be wrong here, what I would suggest is to use perfmon to check the server's performance under both conditions, and then make a decision whether it is worth-it or not to add the wait.

Looking at the tag, I'm assuming you're using MS SQL Server, and not any of the other flavours.

Answer 2

You could delay the procedure, but that might or might not help you. It depends on how the procedure works. Is it in a transaction, why a cursor (horribly inefficient in SQL Server), where is the slowdown, etc. Perhaps reworking the procedure would make more sense.

Answer 3

Ever since SQL 2005 included windowing functions and other neat features, I've been able to eliminate cursors in almost all instances. Perhaps your problem would best be served by eliminating the cursor itself?

Definitely check out Ranking functions http://msdn.microsoft.com/en-us/library/ms189798.aspx and Aggregate window functions http://msdn.microsoft.com/en-us/library/ms189461.aspx

Answer 4

Putting the WAITFOR inside the loop would indeed slow it down and allow other things to go faster. You might also consider a WHILE loop instead of a cursor - in my experience it runs faster. You might also consider moving your cursor to a fast-forward, read-only cursor - that can limit how much memory it takes up.

declare @minid int, @maxid int, @somevalue int 
select @minid = 1, @maxid = 5
while @minid <= @maxid
begin
  set @somevalue = null
  select @somevalue = somefield from sometable where id = @minid
  print @somevalue
  set @minid = @minid + 1
  waitfor delay '00:00:00.1'
end

Answer 5

I'm guessing that whatever code you have means that the other processes can't access the table your cursor is derived from.

Provided that you make the cursor READONLY FASTWORD you should not lock the tables the cursor is derived from.

If, however, you need to write, then WAITFOR wouldn't help. Once you've locked the table, it's locked.

An option would be to snapshot the tables into a temp table, then cursor/loop through that instead. You would then not be locking the underlying tables, but equally the tables could change while you're processing the snapshot...

Dems