Results 1 to 9 of 9

Thread: [SQL Server 2016] Looping Over Records

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    [SQL Server 2016] Looping Over Records

    In the past, when I needed to iterate over a collection of records what I would do is:
    1. Create a temporary table
    2. Insert into the temporary table a sub select of the records I want to iterate over
    3. Start the loop by calling WHILE EXISTS
    4. At the beginning of the iteration, get the topmost record and at the end of the iteration, remove it


    This would translate into something like the following:
    Code:
    DECLARE @TempTable TABLE (Id INT NOT NULL);
    DECLARE @TempId INT;
    INSERT INTO @TempTable SELECT Id FROM SourceTable WHERE SomeCondition = 1;
    WHILE EXISTS(SELECT * FROM @TempTable)
    BEGIN
        -- get the topmost record
        SELECT TOP 1 @TempId = Id FROM @TempTable;
    
        -- do something...
    
        -- delete the topmost record
        DELETE FROM @TempTable WHERE Id = @TempId;
    END;
    However, in working in a legacy application I've noticed that the prior developer did something slightly different. What they did was create a cursor and then used a while loop based on the FETCH_STATUS. E.g.:
    Code:
    DECLARE @TempId INT;
    DECLARE MyCursor CURSOR FOR
        SELECT Id FROM SourceTable WHERE SomeCondition = 1;
    
    OPEN MyCursor;
    
    FETCH NEXT FROM MyCursor INTO @TempId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- do something
    
        FETCH NEXT FROM MyCursor INTO @TempId;
    END
    
    CLOSE MyCursor;
    DEALLOCATE MyCursor;
    I'm wondering what y'all's opinion are on the two. Is there a performance difference between the two? Is one more of a legacy holdover pattern than anything else?

    I'm just curious because the WHILE @@FETCH_STATUS = 0 just seems odd to me.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [SQL Server 2016] Looping Over Records

    Yes, there is a difference between the two. Aaaaannnnnd the way you're doing is far more preferable to using a cursor. Cursors are slow as there's a lot they need to keep track of, and it can cause locks that are difficult to track down (one moment the lock isn't there, then the next row is accessed, suddenly a lock appears, someone else tries to do something, they're locked out, DBA goes in to look, but things have moved on, the lock is no longer there....) I've long since dumped using cursors as a go-to for looping opting instead to dump everything into a table (or at least the minimal info that's needed) and use an IDENTITY field on the temp table, then loop 1-@@ROWCOUNT until I'm done, or no more rows to process (I've had some processes where I have to add rows as I go). I use the counter route rather than deleting because... well, I'd have to have a row identifier anyways to delete it, and a counter is easy, and two, if I need to I can go back a step, and three, it never really occurred to me.
    Anyways, yeah, using cursors like that above fell out of favor some time back. I know I've seen articles and papers on the performance, but that was years ago. One of those things I logged mentally, changed what I was doing, saw a change, and just kept on going.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: [SQL Server 2016] Looping Over Records

    To tell the truth we have a couple of cursos in the databases and I just leave them there because they are working as expected for many years but , yeah , I know.
    So here is an interesting article: https://www.mssqltips.com/sqlservert...ithout-cursor/

    The last time I used a cursor was to iterate through 3 different remote server locations passing params and fetching data with simple and dynamic SQL. I'm sure I could have passed all the servers names and creds into a table and pass a parameter to every single remote server telling them to do stuff but the cursor seemed a lot easier to implement and the remote servers would not require any loops when the focus was on them. So I only looped in a main server containing 7 or 8 different remote servers looping through their tables and then getting back to another main but remote server, . It just seemed more understandable to me than trying to find the @rowcount .
    I'm not saying that it was better I'm just saying that performance wise there was no difference and no "strange" locks could have been made as the focus was on a db table per iteration.
    So I guess I had to find an excuse for cursors here
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [SQL Server 2016] Looping Over Records

    Huh? Why looping at all?
    Code:
    UPDATE SourceTable 
    SET SomeField=NewCalculatedValueFromDoSomeThing 
    WHERE 
    ID IN (SELECT ID FROM SourceTable WHERE SomeCondition=1)
    or am i misunderstanding something here?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: [SQL Server 2016] Looping Over Records

    In this case with the cursor, that's what I'm going to suggest to the client.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [SQL Server 2016] Looping Over Records

    Quote Originally Posted by Zvoni View Post
    Huh? Why looping at all?
    Code:
    UPDATE SourceTable 
    SET SomeField=NewCalculatedValueFromDoSomeThing 
    WHERE 
    ID IN (SELECT ID FROM SourceTable WHERE SomeCondition=1)
    or am i misunderstanding something here?
    Because it isn't always straightforward like that... sometimes it's pulling values out of one table and using them to call another stored proc as parameters... Or there's some other processing that needs to take place that doesn't allow for an update statement. I do (did) a lot of data imports where I have to scrub the imported data... a simple update doesn't always work.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [SQL Server 2016] Looping Over Records

    Quote Originally Posted by techgnome View Post
    Because it isn't always straightforward like that... sometimes it's pulling values out of one table and using them to call another stored proc as parameters... Or there's some other processing that needs to take place that doesn't allow for an update statement. I do (did) a lot of data imports where I have to scrub the imported data... a simple update doesn't always work.

    -tg
    Ahhh... OK. Then i agree.
    Another Use-Case might be an UDF where you have to process multiple records, but return one result (think your own aggregating UDF)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [SQL Server 2016] Looping Over Records

    For simple tasks looping with cursors or temp tables can be rewritten in set-oriented fashion which is the fastest. For RBAR looping obviously there is something more happening inside the loop, e.g. calling a stored procedure, accessing a web-service, so executing the body of the loop takes 99.9% of the time and the fashion in which the loop is constructed is pointless to optimize.

    Regarding cursors in T-SQL there is *enormous* cargo-culting in the industry, that is like millions of articles with rubbish implementations, incl. some in MSDN and/or high-profile consultants.

    First, there are cursor *variables* available since sql2000 (at least) but these are rarely used in sample code for no apparent reason. Everyone insist on using obsolete syntax DECLARE MyCursor CURSOR FOR SELECT ... which does not allow having another cursor named MyCursor being active (which prevents calling the same store procedure recusively for instance).

    Anyway, DECLARE @MyCursor CURSOR variables are keeping the state local, does not spill out into connection namespace and are more performant.

    Another sign of cargo-culting is repeating the anti-pattern of doubling the same FETCH NEXT statement -- clearly something only a system administrator might think is fine and every sane developer will detect the code smell immediately esp. when the cursor fetches more that one column. Why repeat FETCH NEXT FROM MyCursor INTO @TempId before the loop and inside the loop exactly the same? Can this be refactored not to repeat the same statement?

    Here is a single FETCH NEXT loop with a cursor variable we use

    Code:
    SET         @CrsStatus = CURSOR FAST_FORWARD FOR
    SELECT      SeqNo, Name
    FROM        dbo.v_nom_DocStatuses
    ORDER BY    SeqNo
    
    OPEN        @CrsStatus
    
    WHILE       1=1
    BEGIN
                FETCH NEXT
                FROM        @CrsStatus
                INTO        @StatusSeqNo, @StatusName
    
                IF          @@FETCH_STATUS <> 0 BREAK
                
                --- do something
    END
    
    CLOSE       @CrsStatus
    DEALLOCATE  @CrsStatus
    FAST_FORWARD means read-only cursor with performace optimizations enabled which is exactly what you would need for simple RBAR looping with minimal locking on cursor's base tables.

    cheers,
    </wqw>

  9. #9
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: [SQL Server 2016] Looping Over Records

    I took a portion of the cursor I mentioned previously. This will look local and the go to other servers:


    [CODE]
    DECLARE MYCEC CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT [Code], ServerName, DatabaseName, UserID, Pwd
    FROM dbo.tblDW
    WHERE IsActive = 1 AND (Code = @RunForCode OR @RunForCode = '0000')
    ORDER BY
    Code:
        OPEN MYCEC
        FETCH NEXT FROM MYCEC INTO @Code, @ServerName, @DatabaseName, @UserID, @Pwd
    
        WHILE @@FETCH_STATUS = 0 
        BEGIN
    
    
    	  EXECUTE @RC = VDW.dbo.spSYSConnectToServer @ServerName, @DatabaseName, @UserID, @Pwd
            IF @RC <> 0    
            BEGIN
    		-- GOTO ERROR_POINTMYCEC
    		print ' loop error'
    		return
    		End
    		else
    		begin
    		print 'Trying to update unknown data in : ' + @Code
    		end
    
    		/* try to update  data */
    	set @SqlString1 = N'exec DWSRV.Vsrv.dbo.zz_GDPRUsersUnknown @param2'
        set @ParamDef1 = N'@param2 nvarchar(256)'
    
    	EXECUTE  sp_executesql @SqlString1 ,@ParamDef1, @param2 = @Personemail
    	--select @RCOUTER
    		  
            FETCH NEXT FROM MYCEC INTO @Code, @ServerName, @DatabaseName, @UserID, @Pwd
        END
    
        CLOSE MYCEC
        DEALLOCATE MYCEC
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width