Cursors Be Gone!

  • 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

  • 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

  • This would also eliminate the select count(*)

    while (EXISTS(select 'True' From @dblist))

  • 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.

  • 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...

  • 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++){...}

  • 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.

  • 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

  • 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.

  • 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

  • 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:

  • @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

  • 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

  • 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.

  • 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