Replacing Cursors and While Loops

  • Clinton Herring

    Ten Centuries

    Points: 1160


    I just yesterday used the techique at a contract client with similar results although not quite as dramatic as the one I wrote about. To be fair though, there is a limit to how well this may work. After I wrote the article I came across another update that was being handled with a cursor. Within the cursor 18 columns had to be updated while pivoting data. I found I only needed 7 functions to perform the 18 updates but by the time all thoses calls were made for 2.2 milllion records, the new process clock out at the same speed as the old one. I tested individual calls and they were very short but that many had an accumulative affect. The good side though is that the code is so much simpler and easier to read that I plan to replace the old code anyway.

    Have fun building your functions.

  • Jeswanth Jaishanker


    Points: 1869

    Happend to see this while browsing . thought it might be relevant here. have a look



  • Phil.Nicholas


    Points: 2097

    Just a couple of things I've found useful. If you have to loop generally it's quicker to use a while loop selecting each record individually rather than a cursor. Also if you find you have very large SQL statement it often runs much quicker broken up into smaller ones. If each calculation can be broken up into individual set based queries I'm sure you'll improve the performance.

    Phil Nicholas

  • Mark Bolen

    Valued Member

    Points: 54

    I hope there are not many people hitting this table. This may be OK as long as it is a nightly run that you know you are the only code running against it.  

    However long it runs, it will be locked during this process.  Although fast, for databases with a lot of people expecting to update it, it would be unacceptable unless you can get it to run in less than 4 seconds (you last said 26 seconds I believe) and lock timeout's are set well above that.  Speed is not always the only consideration unless you are running exclusively!  In which case I would put a table lock hint on it to speed it up even more.  I would recommend taking a little longer time to make sure you don't have a table lock for a half minute. 


  • Mark Bolen

    Valued Member

    Points: 54

    Here is how a highly available database would look.  I work on databases with hundreds of thousands of users on databases unconceivable large to most.  At my job location, we don't even have a nightly processing time (global app). 

    Whenever I read posts on this and most other sites, I have to keep in mind that they have a low concurrency of users.  As a user base grows, most will have a an increasing number of problems they can't identify over time.

    The following code is focused on removing cursors, actually a lot of code should be added for error checking, but for simplicity and keeping to the topic it is left out.  While loops in our environment are a necessary evil to keep the number of rows being updated down to a small number so they can complete and release locks within 4 seconds (our apps rule; should actually be lower; perhaps 3).  Think availablity first, then speed.  At my work location, we even have variations of this that loop through rows in blocks of 5, 10, or whatever higher number still allows us to complete in less than 4 seconds.  As you can see, it is a completely different mind set.


    Declare @tmp_id int

    Declare @prev_id int

    Declare @tmp_values varchar(20)

    Set LOCK_TIMEOUT = 12000  -- assuming all code in app is tested to have

                                            -- no update take longer than 4 seconds


    Set @prev_id = -1  -- assumes all ID's are positive in nature

    While Exists ( 

                       SELECT TOP 1 @tmp_id = id from DB.dbo.OutPut_tbl WITH(NOLOCK)

                        Where id > @prev_id     -- retrieve next row > than the previous

                        order by id                   -- key data ; preferable clustered ;

                                                          -- order by id to maintain 

                                                           -- a reference point



          --Direct column pivot into a variable

          -- I don't know enough about the column_out field; but lets hope we never 

          -- increase in size greater than 20; declarations and table field would

          -- need to be changed if that 

          -- was possible.

          SELECT @tmp_values = @tmp_values + convert(varchar(20), column_data) + ','

          FROM DB.dbo.many_tbl WITH(NOLOCK)

          WHERE id = @tmp_id


          UPDATE DB.dbo.OutPut_tbl WITH(ROWLOCK) -- assuming that this will take

                                                                       -- less than 4 seconds

                                                                       -- otherwise create another

                                                                       -- while exists loop

          SET column_out = Left(@tmp_values,20)

          WHERE id = @tmp_id

          SET @prev_id = @tmp_id  -- move our pointer to the next row

    END -- While Exists

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    It's just worth pointing out that by default how a cursor and while loop work are different from a transaction view point. A while will execute as a complete transaction - which might bring about its own problems. ( this assumes no explicit transactions defined )

    Just an observation - I like replacing cursors with while loops but you do have to be careful some times.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]

  • Clinton Herring

    Ten Centuries

    Points: 1160

    Good thoughts Mark. Thanks for your input.

  • Vladimir Sim

    Ten Centuries

    Points: 1250

    Hi Herring,

    I read with interest on this topic of the 'replacing cursor and while loop'.

    I faced the same problem on having lot of cursor which I replace with loop, but didn't solve my execution time.

    Your article really help me to look at different way to replace cursor with select statment. I know how to use

    select for updating data using select method from your


    But I cannot think of any good way to insert new data

    using select statement. Do you have any good way to

    insert new data using the select statement which I replace

    from cursor.

    Please advise. thank you

  • Clinton Herring

    Ten Centuries

    Points: 1160

    You can of course use the Values clause but if you want to call functions to generate some new data then use the select statement...insert into >tablecolumnlistfunctionetc. until column list is matched<

  • Vladimir Sim

    Ten Centuries

    Points: 1250

    Hi Herring,

    Below is my example to illustrate my issue.

    From your article, I know how to use function to

    update using select statement.

    But I can't think on how to insert new values

    using select statement. Please illustrate with

    example to guide me. How to convert my example

    into using select statement inserting and updating..

    Thank you.

    declare cur_insert CURSOR FOR

    select ..... FROM

    where xxxx




    Insert into values(xxxx)




  • Doug Hiwiller


    Points: 11

    Perhaps this has already been asked (or scoffed at for being 'undocumented') but have you considered a function using COALESCE to return your pivoted series?


    id INT,

    val VARCHAR(10))

    INSERT Test (id, val) VALUES(56, 'run')

    INSERT Test (id, val) VALUES(56, 'jump')

    INSERT Test (id, val) VALUES(56, 'scale')

    INSERT Test (id, val) VALUES(57, 'fly')

    INSERT Test (id, val) VALUES(57, 'swim')

    INSERT Test (id, val) VALUES(57, 'crawl')


    CREATE FUNCTION uf_pivot_series (@id INT)



    DECLARE @return VARCHAR(8000)

    DECLARE @delimiter CHAR(2)

    SET @delimiter = ', '

    SELECT @return = COALESCE(@return + @delimiter, '') + val FROM Test

    WHERE id = @id

    RETURN @return



    SELECT id,

    dbo.uf_pivot_series(id) as val

    FROM Test

Viewing 11 posts - 16 through 26 (of 26 total)

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