-
Oct 18th, 2021, 02:53 PM
#1
[SQL Server 2016] Looping Over Records
In the past, when I needed to iterate over a collection of records what I would do is:
- Create a temporary table
- Insert into the temporary table a sub select of the records I want to iterate over
- Start the loop by calling WHILE EXISTS
- 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.
-
Oct 18th, 2021, 04:14 PM
#2
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
-
Oct 18th, 2021, 04:48 PM
#3
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 19th, 2021, 09:02 AM
#4
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
-
Oct 19th, 2021, 09:19 AM
#5
Re: [SQL Server 2016] Looping Over Records
In this case with the cursor, that's what I'm going to suggest to the client.
-
Oct 19th, 2021, 12:59 PM
#6
Re: [SQL Server 2016] Looping Over Records
Originally Posted by Zvoni
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
-
Oct 20th, 2021, 01:14 AM
#7
Re: [SQL Server 2016] Looping Over Records
Originally Posted by techgnome
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
-
Oct 20th, 2021, 04:40 AM
#8
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>
-
Oct 20th, 2021, 05:26 AM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|