none
CURSOR Performance and Alternatives RRS feed

  • General discussion

  • This has been discussed in lots of forums, threads, posts and blogs previously.

    People and experts claim to use the either one and most of them are inclined to WHILE loops, without any proof and logic.

    Links: http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/

    http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx

    http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

    ... and many more.

     

    So in reference to Hugo's response in following post I tried to evaluate both the CURSORS and WHILE loops by creating a set of TSQL Script:

     

    use tempdb
    GO
    
    create table T1 (sn int identity(1,1) primary key, data varchar(1000))
    
    insert into T1 (data)
    values (replicate('a',1000))
    GO 10000
    
    select * from T1
    
    create table T2 (sn int primary key, data varchar(1000))
    create table T3 (sn int primary key, data varchar(1000))
    
    -- Test the CURSOR, pull record from T1 and insert into T2
    set nocount on
    
    declare @stDate datetime
    declare @sn int , @data varchar(1000)
    
    set @stDate = getdate()
    
    declare cr cursor
    FORWARD_ONLY FAST_FORWARD
    for select sn, data from T1
    
    open cr
    fetch next from cr into @sn, @data
    while @@fetch_status=0
    begin
    	insert into T2
    	select @sn, @data
    	fetch next from cr into @sn, @data
    end
    close cr
    deallocate cr
    
    select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me 966, 413, 306, 306 ms
    GO
    
    -- Test the WHILE loop with counter, pull record from T1 and insert into T3
    set nocount on
    
    declare @stDate datetime
    declare @ctr int
    
    set @stDate = getdate()
    set @ctr=0
    
    while @ctr<=10000
    begin
    	insert into T3
    	select sn, data
    	from T1
    	where sn = @ctr
    	
    	set @ctr = @ctr + 1
    end
    
    select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me: 1070, 450, 376, 423 ms
    GO
    
    --select * from T2
    --select * from T3
    
    drop table T2
    drop table T3
    drop table T1

    I ran the above code for CURSOR & WHILE loop 4 times and it gave me less execution time for CURSOR, everytime.

    I was not able to check its execution plan graphically or by setting statistics profile on.

    Does this prove that CURSORs are more performant than WHILE loops? If no, then what are the other factors people dislike CURSORs.
    What all other benchmarks strategies I can check?

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Thursday, December 23, 2010 5:55 AM

All replies

  • Manu,

      Brad Schulz has some excellent 3 articles about Cursors. Yes, Cursors will perform way better than While Loops. you can read those articles.

    http://bradsruminations.blogspot.com/search/label/Cursors

    read all 3 parts one by one...

    Thursday, December 23, 2010 6:22 AM
  • Thanks @Ramireddy,

    The links provided by you are very informative and supports Hugo's comments on favor of CURSORS.

     

    Just want to know other than calculating time-difference what is the other way I can evaluate performance of both the scripts?

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Thursday, December 23, 2010 2:31 PM
  • Does this prove that CURSORs are more performant than WHILE loops? If no, then what are the other factors people dislike CURSORs


    I can only guess, and I will.

    IMO, cursors are a little more difficult to write than While loops. But that is not what it is about. It is not about when you need sequential processing (or rather, when sequential processing is the best solution) whether you use a cursor or a While loop.

    The point is, that are just a few topics where sequential processing gives better performance than using a set based approach. So in most cases where a cursor is used, it is used because the developer was not able to come up with a set based approach (or never tried in the first place). For most topics, a set based approach outperforms a cursor orders of magnitude, even more so if the set based approach can leverage parallellism.


    Gert-Jan
    Thursday, December 23, 2010 7:06 PM
  • > Does this prove that CURSORs are more performant than WHILE loops? If no, then what are the other factors people dislike CURSORs.

    We don't dislike cursorss because WHILE loops are better. WHILE loops are in fact often worse. I've seen more than one case where people have iterated over a temp table with a WHILE loop with SELECT MIN or similar. And the temp table has not had the index to support the loop.

    As Gert-Jan says, we dislike cursors, because most often there is a set-based solution which is far more efficient.

    But there are exceptions to everything. There are situations where a cursor beats the corresponding set-based query with a wide margin. And there are situations when a WHILE loop is the only alternative for iteration.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, December 23, 2010 11:23 PM
  • Just want to know other than calculating time-difference what is the other way I can evaluate performance of both the scripts?

     

     

    Apart from time, No oF Reads, Writes also will be considered to measure query performance.

    you can see those by "set statistics io on"

    Friday, December 24, 2010 4:49 AM
  • Thanks Ramireddy,

    When I use "set statistics io on" it gives me thousand lines of messages with details of each iteration. Same is with "set statistics profile on" & "set statistics time on".

    I'm looking for a consolidated total instead of fragments, at least at the end.

     

    Don't know if I'm looking at the wrong way to achieve this.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Friday, December 24, 2010 10:40 AM
  • > Just want to know other than calculating time-difference what is the other way I can evaluate performance of both the scripts?

    Wall-clock time is in my opinion the best measurement of performance.

    But if you want to measure reads and writes, but you could package the script in a stored procedure, and then look at the SP:Completed event in Profiler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, December 24, 2010 4:41 PM
  • > Just want to know other than calculating time-difference what is the other way I can evaluate performance of both the scripts?

    Wall-clock time is in my opinion the best measurement of performance.

    But if you want to measure reads and writes, but you could package the script in a stored procedure, and then look at the SP:Completed event in Profiler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)

    Thanks Erland,

    Yes I did that by creating a stored-proc of the script with "set nocount on" option, when I run the proc it still executes for each iteration, but at last give me the consolidated time estimate.

    Works!

     

    Thanks Much! to all of you.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Saturday, December 25, 2010 4:10 AM