Post reply

Cursor or not

  • February 13, 2006 at 8:11 am

    #93380

    Hi,

    Is there any advantage of using cursor over the following approach?

    SELECT @rowCount = 1

    SELECT @maxRows = count(rowNum)from @tempTable

    WHILE(@rowCount<=@maxRows)
    BEGIN
    INSERT INTO dbo.TableA (Col1, Col2, Col3)
    SELECT Val1, Val2, Val3 FROM @tempTable WHERE rowNum=@rowCount

    SELECT @rowCount = @rowCount +1
    END

    Thank you for reading,
    R

  • February 13, 2006 at 9:42 am

    #620475

    Try to avoid cursor at all cost.  I got yelled at by my old DBA all the time when I used cursor. 

    Your code is fine.

  • February 13, 2006 at 10:53 am

    #620489

    unless you need to handle the @@IDENTITY /SCOPE_IDENTITY() to insert into child records after you insert into a parent table, there's no real need to use the cursor in your example;

    a simple

    INSERT INTO dbo.TableA (Col1, Col2, Col3)

    SELECT Val1, Val2, Val3 FROM @tempTable

     

    will insert every row from @temptable into your tableA, and much faster thant he cursor;

    many inserts and updates can be performed without a cursor; but sometimes you just gotta do it row by row if you are dealing with clild/dependant data.

     

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 13, 2006 at 1:26 pm

    #620527

    There's not that much difference between a cursor that processes a set of records one by one and a while loop that processes a set of records one by one.

    They're both using procedureal style code instead of set-based code.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 15, 2006 at 4:24 am

    #620951

     

    cursors are used when,

    if you have bad database design.

    my recommendation is to you pls. do not use

    cursors.

     

  • February 15, 2006 at 6:59 am

    #621014

    OK folks I will chime in becuase I think a common theme is seen here however cursors are simply a tool to handle conditional logic where a set based solution cannot be found. Generally after writing a cursor you will find a set based way or the cursor may be extremely slow so you dig for alternate methods. Cursor are generally poor substitutes for complex set logic and you should avoid cursors whereever you can but not at the cost of performance. If you don't readily see a set based solution consider a cursor or even a while loop and try seeing if you see the set based solution then. I have written some queries more than a dozen times and even had to start out as a cursor on a few to get where I really wanted and needed to be. But as for your example I think the set based solution given as an example is better but the while will generally outperform the cursor in this case (except maybe a cursor with Fast Foward as the direct logic and then it would be close).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply