Row-By-Row Processing Without Cursor

  • John Beggs

    SSCommitted

    Points: 1921

    All of this seems like a lot of work to replace a cursor in a row by row process is needed.

    Why is it that people don't seem to understand that cursors are only bad if you use them where a set based solution could have been used?

    What's more, while I have no issue with using a table variable or temp table when needed, I challenge you to make either of them out perform a properly formed cursor.  FAST_FORWARD anyone?

  • Idea Deadbeat

    SSC-Addicted

    Points: 416

    If the end goal isn't immediate row by row processing - don't forget about the SQL 2005 OUTPUT clause that can be used with INSERT, UPDATE and DELETE. 

     

  • kevin mann

    Hall of Fame

    Points: 3894

    what's the difference betweren FAST_FORWARD and FORWARD_ONLY STATIC READ_ONLY? I have no clue why I have always overlooked FAST_FORWARD.

  • John Beggs

    SSCommitted

    Points: 1921

    FAST_FORWARD is FORWARD_ONLY and READ_ONLY together.

  • David.Poole

    SSC Guru

    Points: 75401

    I have seen cursors out-perform a non-cursor method of looping through records.

    The key is knowing exactly when a cursor is necessary or alternatively, when some nifty set based SQL will do the job.

    I you come from a procedural language background then cursors feel more natural to use. They are easy to understand.

    If your background is set based then you will be more comfortable using some other alternative.

    I find the main problem with cursors is when someone writes on that has multiple joins on many tables. This causes all sorts of locking issues. The better solution is to bounce the records you need out to a temp table and loop through the temp table.

    The caveat to the above is where you have to protect against changes while the cursor is running.

    Perhaps this thread represents an opportunity for a SSC competition. Set a problem that seems to demand a cursor and see if someone can come up with a set bases solution.

  • Dmytro Andriychenko

    SSC Enthusiast

    Points: 181

    Hi,

    I think some people might be missing the point of this article. The author has just suggested a very graceful alternative to using a cursor when bulding some set of statements for example. Cursors have their use, no doubt! In many places SQL Server offers a multitude of ways to do the same thing - one just has to exercise common sence and issues of performance and security when choosing the most appropriate. It certainly does not hurt to know the choices. I for one did not know one can aggreagate string fields in this way - have already found a use for the idea in my work!

    Thank you to the author, great stuff!

    Kindest regards,

    Dmytro

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    I think it's a horrible idea, should be mush slower than a cursor in most cases, and more sophisticated.

  • Dan Smith-306792

    SSC Enthusiast

    Points: 159

    Another approach would be to use functions instead of a procedure in the trigger. For example:

    create table Books (

    BookCode varchar(5),

    BookDesc varchar(100))

    go

    -- The function processes a new BookCode during insertion

    create function fnBookProcess (

    @BookCode varchar(5)

    )

    returns varchar(200)

    as

    begin

    -- Do something useful with each BookCode.

    -- Just return a message

    return('Function is processing... ' + @BookCode)

    end

    go

    -- trigger runs a select calling the fnBookProcess for each inserted row on the Books table

    create trigger tr1 on Books after insert as

    print 'Trigger is calling fnBookProcess'

    select dbo.fnBookProcess(BookCode) from Inserted

    go

    -- insert books for testing

    insert Books

    select 'A','Book desc 1' union all

    select 'B','Book desc 2' union all

    select 'C','Book desc 3'

    Here the fnBookProcess function is called for each line of the Inserted virtual table and it avoids the 8000 varchar limit. The only draw back is that now the insert statement returns a recordset from the trigger's select statement. I don't know how to avoid that.

  • Jose-cin

    SSC-Addicted

    Points: 487

    Hello from Peru 😀 ,

    How can i apply this if I'm using sql server 2000? (because the problem of the variable @sql =varchar(8000), in sql server 2000 doesnt exist the data type varchar(max)).

    I have the same problem of Dan because idont want to get a result when I'm going to call the function, there is an option to dont get the result of the sentence "select dbo.fnBookProcess(BookCode) from Inserted"?.

    Thanks

    Jose Gutierrez

    PD: (Sorry about my english :S).

    José Miguel Gutiérrez Hilaccama
    Database Developer - MCTS SQL Server 2005
    No way Jose? ... bah

  • Dan Smith-306792

    SSC Enthusiast

    Points: 159

    Sorry, I just realized that user defined functions can't make any changes to data, so insert, update and delete statements can't be called from within a function.

  • Jeff Moden

    SSC Guru

    Points: 997348

    shashi kant (12/7/2006)


    Instand of using this thing u can use the Table variable while loop which help lot compare to Cursor...

    Actually, that's an old wive's tale... a "Firehose" cursor is just as fast as a temp table and a While loop... both use TempDB and neither will lock source resources, either.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 997348

    jose (12/6/2007)


    Hello from Peru 😀 ,

    How can i apply this if I'm using sql server 2000? (because the problem of the variable @sql =varchar(8000), in sql server 2000 doesnt exist the data type varchar(max)).

    I have the same problem of Dan because idont want to get a result when I'm going to call the function, there is an option to dont get the result of the sentence "select dbo.fnBookProcess(BookCode) from Inserted"?.

    Thanks

    Jose Gutierrez

    PD: (Sorry about my english :S).

    First, you shouldn't do this... you should write the correct set-based code...

    Second, if you really must do this because you can't think of the necessary set-based code... then use more than 1 variable to hold the dynamic SQL. Then execute it like this...

    EXEC (@SQL1+@SQL2+@SQL3).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ion Freeman

    Say Hey Kid

    Points: 701

    The whole reason I don't write articles like this is because I don't want to do the heavy lifting around collecting metrics. So, I'd like to see that this is actually faster or less resource intensive than a cursor for realistic uses.

  • SQLGuy64

    Ten Centuries

    Points: 1061

    I do row by row processing by using a do while loop for example:

    declare @LoadedKey BIT

    SET @LoadedKey = NULL

    SET @LoadedKey =

    (

    SELECT

    TOP 1 ColA

    FROM

    BusinessTable

    WHERE

    LOADED =

    (

    SELECT

    IDCol

    FROM

    BusinessTable

    WHERE

    FlagCol = 0 AND

    ERROR_CODE ='0'

    ORDER BY IDCol)

    WHILE @LoadedKey IS NOT NULL

    BEGIN

    --BusinessLogic

    SET @LoadedKey =

    (

    SELECT

    TOP 1 ColA

    FROM

    BusinessTable

    WHERE

    LOADED =

    (

    SELECT

    IDCol

    FROM

    BusinessTable

    WHERE

    FlagCol = 0 AND

    ERROR_CODE ='0'

    ORDER BY IDCol

    )

    CONTINUE

    END

  • Jeff Moden

    SSC Guru

    Points: 997348

    Ion Freeman (12/28/2007)


    The whole reason I don't write articles like this is because I don't want to do the heavy lifting around collecting metrics. So, I'd like to see that this is actually faster or less resource intensive than a cursor for realistic uses.

    There's no difference between a properly formed "Firehose" cursor (Fast Foward) and a While loop except that the cursor is easier to use... 😉

    The real key is to avoid the loop for processing sets of data altogether. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 31 through 45 (of 71 total)

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