none
Cursor much faster when loop. What's wrong ?

    Question

  • Hello All!

    I tried to modify script (not my) and this script contains the cursor inside:

    SELECT TOP(100) * INTO #tmpTable FROM MyTable WHERE MyDate<@ThisDate

    DECLARE My_Cursor CURSOR FOR SELECT ID, convert( varchar(max), MyRequest) AS MyResponse FROM #tmpTable WHERE ID > 0 AND MyNumber > 0 OPEN My_Cursor FETCH NEXT FROM My_Cursor INTO @ID, @Response WHILE @@FETCH_STATUS = 0 BEGIN

    --Some T-SQL code

    WHILE @MyVar <> 0 BEGIN --Some T-SQL code in loop with modification of @MyVar

    END FETCH NEXT FROM MY_Cursor INTO @ID, @Response END CLOSE My_Cursor DEALLOCATE My_Cursor

    I replaced this cursor on loop as follows:

    SELECT TOP(100) *, ROW_NUMBER() OVER(ORDER BY MyRecord) AS '_Counter' INTO #tmpTable FROM MyTable WHERE MyDate < @ThisDate

    DECLARE @cnt int SELECT @cnt = MAX(_Counter) FROM #tmpTable WHILE(@cnt >0) BEGIN SELECT @quid = QUID, @Response =convert(varchar(max),OFXRequest) FROM #tmpLog WHERE QUID > 0 AND OFXResSize > 0 AND _Counter = @cnt --Some T-SQL code WHILE @MyVar <> 0 BEGIN --Some T-SQL code in loop with modification @MyVar END SET @cnt = @cnt - 1 END

    When I tested modified script without cursor - it worked 1 hour and 5 minutes. Script with cursor works 13 minutes.

    Why script with cursor so much faster than without cursor ?
    What's wrong in my code ?

    Thanks

    Andy


    Andy Mishechkin

    Wednesday, September 04, 2013 6:23 PM

Answers

All replies

  • Have a look at this article:

    Comparing cursor vs. WHILE loop performance in SQL Server 2008


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    • Edited by Saeid Hasani Friday, September 06, 2013 2:35 PM
    • Proposed as answer by Saeid Hasani Friday, September 06, 2013 2:35 PM
    • Marked as answer by Andy Mishechkin Friday, September 06, 2013 4:45 PM
    Wednesday, September 04, 2013 7:12 PM
  • Perhaps the better question is why you expected a "loop" to be an improvement.  In general, you are much better off using set-based logic rather than sequential row-based logic.  A simple substitution of a  loop for a cursor for no particular reason is unlikely to be beneficial. 

    In addition, I'll point out another flaw in your logic that you might not realize.  You use top 100 in your first query - but there is no order by clause.  While you might get the "top 100" rows you expect, it is equally likely that you can get a random set of 100 rows.  Perhaps your goal does not depend on which rows are actually selected - frequently that is an overlooked logic error that is only discovered once the code reaches production (or encounters a table of significant size).

    Wednesday, September 04, 2013 7:56 PM