Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursors Be Gone!


Cursors Be Gone!

Author
Message
Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
Having modified that script a bit, and run it on my server - the time was 23.078 secs. Improved, certainly, but not better.


SET NOCOUNT ON
declare @query varchar(100), @id int
DECLARE @muchWorseProblem TABLE (ID int primary key clustered)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @id2 int;
declare @i int;
set @i = 0;

while 1=1
begin
select top 1 @id2 = ID from @muchWorseProblem

if @id2 is null
break;

select @query = 'declare @i int; set @i = ' + convert(varchar,@id2)
exec(@query)
delete from @muchWorseProblem where id = @id2
set @id2 = null
end



The quickest I could get it to go was the following, which does come in slightly quicker than a cursor, at 10.703 secs:


SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE @muchWorseProblem TABLE (RowNum int IDENTITY(1, 1) primary key clustered, ID int)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @maxRowNum int
SELECT @maxRowNum = MAX(RowNum) FROM @muchWorseProblem

declare @i int;
set @i = 1;

while @i <= @maxRowNum
begin
select @id = ID from @muchWorseProblem WHERE rowNum = @i

select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
exec(@query)

set @i = @i + 1
end



Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
hfxDBA
hfxDBA
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 490
I've always resisted responding to articles with obvious errors simply not wanting to get into arguments about which way to skin the cat. I hope people who read the original articles here also read all the comments and replies. I hear and see too many junior (and some not so junior) read articles then start using the techniques as THE way to do something.

I can spot several things I would change with a lot of the reply scripts. I won't list those right now because I think the most important thing for readers who visit here to know is that they MUST try different techniques and record the results so that they know which technique solves their problem best for them. They must decide on what trade-offs they can risk and which they can't.

Whenever I write an SP, USP, script, or even a TRIGGER, I comment exactly what I did and also comment exactly what alternate methods I used and why I chose the solution I did (for instance, why I chose a table variable over a temp table (or vice versa) or why I chose a cursor over a while loop). If you have several developers or DBAs who could be maintaining the code, this will help them understand your methods and may save them some time down the road.
Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
If you can spot several things you would change, then please let us know. It's not about having an argument, it's about learning, and if you have a good method, then please do share it! BigGrin

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 2946
I'm a bit puzzled by this article. Surely, in order to illustrate the dangers of using cursors or 'WHILE' loops, you would expect to see an example where there is an obvious performance or resource advantage in avoiding cursors. This routine is so trivial that it could be, and has been, written any number of ways without affecting its execution time at all. The trick, surely, is to take a problem that seems impossible to do without cursors and show how easy it actually is. Give the performance figures for a whole range of data sizes etc.

I'm perfectly happy to use WHILE loops for a job like the one described in this article, especially if it makes the routine more understandable. Am I wrong to do so?


Best wishes,

Phil Factor
Simple Talk
Lawrence-865109
Lawrence-865109
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 51
I am very happy that this article was written. However, the speed increases were compared to each other by using percentage of time relative to the batch, which is misleading. For example, 69% of 1 second is a faster process than 31% of 4 seconds.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
Matt Whitfield (12/24/2008)
Having modified that script a bit, and run it on my server - the time was 23.078 secs. Improved, certainly, but not better.


SET NOCOUNT ON
declare @query varchar(100), @id int
DECLARE @muchWorseProblem TABLE (ID int primary key clustered)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @id2 int;
declare @i int;
set @i = 0;

while 1=1
begin
select top 1 @id2 = ID from @muchWorseProblem

if @id2 is null
break;

select @query = 'declare @i int; set @i = ' + convert(varchar,@id2)
exec(@query)
delete from @muchWorseProblem where id = @id2
set @id2 = null
end



The quickest I could get it to go was the following, which does come in slightly quicker than a cursor, at 10.703 secs:


SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE @muchWorseProblem TABLE (RowNum int IDENTITY(1, 1) primary key clustered, ID int)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @maxRowNum int
SELECT @maxRowNum = MAX(RowNum) FROM @muchWorseProblem

declare @i int;
set @i = 1;

while @i <= @maxRowNum
begin
select @id = ID from @muchWorseProblem WHERE rowNum = @i

select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
exec(@query)

set @i = @i + 1
end




Can i suggest that this might be even quicker?:

SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE myCursor CURSOR FORWARD_ONLY READ_ONLY
FOR select top 100000 ID from [tblLogTransactions]

OPEN myCursor

FETCH NEXT FROM myCursor
INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN

select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
exec(@query)

FETCH NEXT FROM myCursor
INTO @id

END
CLOSE myCursor
DEALLOCATE myCursor


Steve Reich
Steve Reich
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 52
Hello all,

I've seen lots of alternatives proposed for replacing cursors, but it's hard to judge which is best based on the theoretical discussions. Has anyone compared completion time of the various cursor replacement techniques against a common set of data?

Thanks,
Steve
lfontanarosa
lfontanarosa
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 55
This would also eliminate the select count(*)

while (EXISTS(select 'True' From @dblist))
Rob Reid-246754
Rob Reid-246754
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 446
I remember some years reading an article similar to this about how to replace CURSORS with WHILE loops and the huge
debate about how much better it was for performance due to the overhead of generating and maintaining a CURSOR.
I went off and wrote myself a test script to compare the two methods. Clearing the cache before each run and testing with numerous sized
datasets. I found that for small datasets the performance was neglible but the larger the dataset grew the worse the TEMP table/WHILE loop performed in relation
to the CURSOR which had a steady duration increase.
As Jeff says if you are only needing to loop one way through a recordset then by adding FORWARD_ONLY and READ_ONLY to the cursor you
are replicating the WHILE loop as a copy of the dataset will be created and used by the CURSOR.
If I have to use a RBAR to solve a problem then I tend to use CURSORS for large datasets for speed and for small datasets a WHILE loop.
Horses for courses and all that.
bob.willsie
bob.willsie
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 151
As a grizzled old coot that is new to SQL, but very old to programming, it appears as though the real answer on which way is better is the same as it always has been:

It depends...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search