December 24, 2008 at 6:16 am
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
December 24, 2008 at 6:22 am
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
December 24, 2008 at 6:26 am
This would also eliminate the select count(*)
while (EXISTS(select 'True' From @dblist))
December 24, 2008 at 6:28 am
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.
December 24, 2008 at 6:29 am
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...
December 24, 2008 at 7:00 am
if you want to avoid the delete I would try it this way:
BEGIN
--
DECLARE @query VARCHAR(100), @dbname SYSNAME, @i INT, @end INT
DECLARE @dblist TABLE (seq INT IDENTITY(1,1) PRIMARY KEY, dbname sysname)
--
INSERT INTO @dblist(dbname)
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
--> this is done so you don't have to check the count every time you loop
SELECT @end = @@ROWCOUNT, @i = 1
WHILE @i <= @end BEGIN
--> quick query to grab the item by the counter
SELECT @dbname = dbname from @dblist WHERE seq = @i
--> same stuff as in the original
SELECT @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
EXEC(@query)
--> increment the counter
SELECT @i = @i + 1
--
END
END
GO
this is more like a for loop in C# now...
for(int i; i < array.length; i++){...}
December 24, 2008 at 7:01 am
Does anyone know where all this negativity came from about cursors? All I've read about this year are articles saying get away from cursors, "Very Bad" for your production environment. Bob, I totally agree with you. You have to look at different solutions as not every situation is the same. FYI, YES, in our Production environments, we use various solutions to address the business needs and as always, we test, test, test, for the best solution. Sometimes that solution is a cursor.
As a side note, I've been in interviews and the question has came up about my views on Cursors. WOW! Talk about all the negative views on them. Maybe in 2009, we should all write our own articles about the proper uses of cursors and see if we can't change this.
December 24, 2008 at 7:08 am
Sigh...
The while loop I used was only a proof of concept, and was designed to show people the mechanics of breaking out of a cursor mindset. They are both still loops, but the overhead of a cursor is much higher.
If you can get better performance out of a set based query, by all means do so. If you have to use a loop, try the more conventional ones. If you have an issue using the more typical while loops, you may have to use cursors. Perhaps I didn't make it that clear in the article.
I'll focus on a more realistic scenario next time.
Merry Christma-Hannu-Kwanza Everybody!
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
December 24, 2008 at 7:17 am
Shane Redding (12/24/2008)
Does anyone know where all this negativity came from about cursors? All I've read about this year are articles saying get away from cursors, "Very Bad" for your production environment. Bob, I totally agree with you. You have to look at different solutions as not every situation is the same. FYI, YES, in our Production environments, we use various solutions to address the business needs and as always, we test, test, test, for the best solution. Sometimes that solution is a cursor.As a side note, I've been in interviews and the question has came up about my views on Cursors. WOW! Talk about all the negative views on them. Maybe in 2009, we should all write our own articles about the proper uses of cursors and see if we can't change this.
I thought it was April Fools day when I read the original article and the positive comments from some of the members about the article. Gaby Abed has taken an example that would perform perfectly well with a cursor and replaced it with some more complicated code that does a worse job. Does anyone review the articles that are put on this site before they are published?
He now thinks that his spelling mistakes were what is wrong with his articled. His spelling mistakes are the least thing wrong with the article. Though if I were publishing an article on a web site I would at least run it through a spell-checker out of respect for people that have to read the thing. The whole article is crap.
December 24, 2008 at 7:18 am
jacroberts (12/24/2008)
Shane Redding (12/24/2008)
Does anyone know where all this negativity came from about cursors? All I've read about this year are articles saying get away from cursors, "Very Bad" for your production environment. Bob, I totally agree with you. You have to look at different solutions as not every situation is the same. FYI, YES, in our Production environments, we use various solutions to address the business needs and as always, we test, test, test, for the best solution. Sometimes that solution is a cursor.As a side note, I've been in interviews and the question has came up about my views on Cursors. WOW! Talk about all the negative views on them. Maybe in 2009, we should all write our own articles about the proper uses of cursors and see if we can't change this.
I thought it was April Fools day when I read the original article and the positive comments from some of the members about the article. Gaby Abed has taken an example that would perform perfectly well with a cursor and replaced it with some more complicated code that does a worse job. Does anyone review the articles that are put on this site before they are published?
He now thinks that his spelling mistakes were what is wrong with his articled. His spelling mistakes are the least thing wrong with the article. Though if I were publishing an article on a web site I would at least run it through a spell-checker out of respect for people that have to read the thing. The whole article is crap.
Ouch!
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
December 24, 2008 at 7:19 am
I believe that Abed misses or eludes the entire point / need for the occasional use of cursors, bad though they may be:
To loop through a record set and take some sort action(s) (possibly updating select columns) based on complex procedural logic, possibly involving other database access to adhere to business rules, that cannot be accomplished via set-at-a-time constructs.
I would be happy to contribute TWO examples that I do not think could be accomplished WITHOUT cursors.
Perhaps our resident 'expert' on bad cursors (as opposed to good cursors) would be so kind as to enlighten me as to the proper manner in which change my evil cursor ways.
:rolleyes:
December 24, 2008 at 7:48 am
@jacroberts -
I didn't get any faster results out of the firehose than I did out of the select-only method. I ran it a few times, they came out pretty much on par.
@jeff -
I tried to test the COALESCE method, but it was horrifically slow over about 10,000 rows. Also interested why you say SELECT COALESCE(@SQL .... when it's actually a lot quicker to just set @SQL to '' first?
@Gaby -
Next time - do the performance tests!! Proving the concept of a while loop isn't necessarily of great value!
@All -
My personal draw from this thread is that yes - you should avoid cursors. Why? Because if you are using a cursor you are probably engaged in a RBAR style operation, which is going to kick you in the *** when you have to do it over a lot of rows.
HOWEVER
If you must use an RBAR for whatever reason, then an appropriately configured cursor will perform on a par with an appropriately configured while loop for a small number of rows. For a larger number of rows, the performance scaling is more linear with a cursor. So just stick to the cursor if you have to do something RBAR style, and stop trying to invent new ways to do the same thing - because doing that just shows that you have not understood *why* cursors are bad.
I'm out - it's Christmas!!!
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
December 24, 2008 at 7:58 am
I have found the following two articles by Hugo Kornelios very informative with regards to cursor optimizations: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx and http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/28/poor-men-see-sharp-ndash-more-cursor-optimization.aspx.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 24, 2008 at 8:05 am
Clearly DBCC against all databases will run almost identically the same time no matter which kind of loop you use to invoke it. I failed to see the point of the article.
December 24, 2008 at 8:33 am
Matt Whitfield (12/24/2008)
@jacroberts -I didn't get any faster results out of the firehose than I did out of the select-only method. I ran it a few times, they came out pretty much on par.
You might get more accurate results if you cut out the noise from the test so you are just testing the looping method i.e remove the exec bit of the code i.e:
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
set @i = @i + 1
end
vs:
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
FETCH NEXT FROM myCursor
INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
The results are pretty much equal on my machine the non-cursor based code runs about 10% slower. With both methods it is being executed line by line or as Jeff Moden would say 'RBAR'. Maybe using Jeff's suggestion of using COALESCE to create a string with all the code in would be better but I don't think the string manipulation in SQL Server is very efficient with a large number of rows as SQL Server has the same problem with string manipulation as you get would get an .NET application if you didn't use a StringBuilder object. In fact Jeff's code below should win the prize for the least scalable method. The time it takes to run for n rows is goes up O(n2).
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL+CHAR(10),'') + 'declare @i int; set @i = ' + convert(varchar,id)
FROM [tblLogTransactions]
PRINT @SQL
EXEC (@SQL)
I think all my Christmas spirit went for a while but I can feel it coming back now.
Happy Xmas
Ebenezer Scrooge
Viewing 15 posts - 16 through 30 (of 272 total)
You must be logged in to reply to this topic. Login to reply