Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Cursors Be Gone! Expand / Collapse
Author
Message
Posted Wednesday, December 24, 2008 4:35 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, 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
Post #625284
Posted Wednesday, December 24, 2008 5:39 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 7:44 AM
Points: 15, Visits: 453
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.

Post #625304
Posted Wednesday, December 24, 2008 5:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, 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! :D

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #625308
Posted Wednesday, December 24, 2008 5:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 588, Visits: 2,554
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
Post #625312
Posted Wednesday, December 24, 2008 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 4:05 PM
Points: 2, Visits: 44
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.
Post #625321
Posted Wednesday, December 24, 2008 6:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
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

Post #625323
Posted Wednesday, December 24, 2008 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 14, 2011 7:37 AM
Points: 106, Visits: 51
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
Post #625327
Posted Wednesday, December 24, 2008 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 10:11 AM
Points: 1, Visits: 55
This would also eliminate the select count(*)

while (EXISTS(select 'True' From @dblist))
Post #625330
Posted Wednesday, December 24, 2008 6:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
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.
Post #625331
Posted Wednesday, December 24, 2008 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 2, 2009 6:43 AM
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...
Post #625332
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse