What is an alternative to cursors for sql looping?
Solution 1
You can do a WHILE
loop, however you should seek to achieve a more set based operation as anything in SQL that is iterative is subject to performance issues.
http://msdn.microsoft.com/en-us/library/ms178642.aspx
Solution 2
Here is the example using cursor:
DECLARE @VisitorID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
-- declare cursor called ActiveVisitorCursor
DECLARE ActiveVisitorCursor Cursor FOR
SELECT VisitorID, FirstName, LastName
FROM Visitors
WHERE Active = 1
-- Open the cursor
OPEN ActiveVisitorCursor
-- Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveVisitorCursor INTO @VisitorID, @FirstName, @LastName
-- perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
Exec MyCallingStoredProc @VisitorID, @Forename, @Surname
-- get next row of cursor
FETCH NEXT FROM ActiveVisitorCursor INTO @VisitorID, @FirstName, @LastName
END
-- Close the cursor to release locks
CLOSE ActiveVisitorCursor
-- Free memory used by cursor
DEALLOCATE ActiveVisitorCursor
Now here is the example how can we get same result without using cursor:
/* Here is alternative approach */
-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #ActiveVisitors (
RowID int IDENTITY(1, 1),
VisitorID int,
FirstName varchar(30),
LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCounter int
DECLARE @VisitorID int, @FirstName varchar(30), @LastName varchar(30)
-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #ActiveVisitors (VisitorID, FirstName, LastName)
SELECT VisitorID, FirstName, LastName
FROM Visitors
WHERE Active = 1
-- Get the number of records in the temporary table
SET @NumberRecords = @@RowCount
--You can use: SET @NumberRecords = SELECT COUNT(*) FROM #ActiveVisitors
SET @RowCounter = 1
-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCounter <= @NumberRecords
BEGIN
SELECT @VisitorID = VisitorID, @FirstName = FirstName, @LastName = LastName
FROM #ActiveVisitors
WHERE RowID = @RowCounter
EXEC MyCallingStoredProc @VisitorID, @FirstName, @LastName
SET @RowCounter = @RowCounter + 1
END
-- drop the temporary table
DROP TABLE #ActiveVisitors
Solution 3
"NEVER use Cursors" is a wonderful example of how damaging simple rules can be. Yes, they are easy to communicate, but when we remove the reason for the rule so that we can have an "easy to follow" rule, then most people will just blindly follow the rule without thinking about it, even if following the rule has a negative impact.
Cursors, at least in SQL Server / T-SQL, are greatly misunderstood. It is not accurate to say "Cursors affect performance of SQL". They certainly have a tendency to, but a lot of that has to do with how people use them. When used properly, Cursors are faster, more efficient, and less error-prone than WHILE
loops (yes, this is true and has been proven over and over again, regardless of who argues "cursors are evil").
First option is to try to find a set-based approach to the problem.
If logically there is no set-based approach (e.g. needing to call EXEC
per each row), and the query for the Cursor is hitting real (non-Temp) Tables, then use the STATIC
keyword which will put the results of the SELECT
statement into an internal Temporary Table, and hence will not lock the base-tables of the query as you iterate through the results. By default, Cursors are "sensitive" to changes in the underlying Tables of the query and will verify that those records still exist as you call FETCH NEXT
(hence a large part of why Cursors are often viewed as being slow). Using STATIC
will not help if you need to be sensitive of records that might disappear while processing the result set, but that is a moot point if you are considering converting to a WHILE
loop against a Temp Table (since that will also not know of changes to underlying data).
If the query for the cursor is only selecting from temporary tables and/or table variables, then you don't need to prevent locking as you don't have concurrency issues in those cases, in which case you should use FAST_FORWARD
instead of STATIC
.
I think it also helps to specify the three options of LOCAL READ_ONLY FORWARD_ONLY
, unless you specifically need a cursor that is not one or more of those. But I have not tested them to see if they improve performance.
Assuming that the operation is not eligible for being made set-based, then the following options are a good starting point for most operations:
DECLARE [Thing1] CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC
FOR SELECT columns
FROM Schema.ReadTable(s);
DECLARE [Thing2] CURSOR LOCAL READ_ONLY FORWARD_ONLY FAST_FORWARD
FOR SELECT columns
FROM #TempTable(s) and/or @TableVariables;
Solution 4
Common Table Expressions would be a good alternative as @Neil suggested. Here's an example from Adventureworks:
WITH cte_PO AS
(
SELECT [LineTotal]
,[ModifiedDate]
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
),
minmax AS
(
SELECT MIN([LineTotal]) as DayMin
,MAX([LineTotal]) as DayMax
,[ModifiedDate]
FROM cte_PO
GROUP BY [ModifiedDate]
)
SELECT * FROM minmax ORDER BY ModifiedDate
Here's the top few lines of what it returns:
DayMin DayMax ModifiedDate
135.36 8847.30 2001-05-24 00:00:00.000
129.8115 25334.925 2001-06-07 00:00:00.000
Solution 5
I have to use a forward cursor, but I don't want to suffer poor performance. Is there a faster way I can loop without using cursors?
This depends on what you do with the cursor.
Almost everything can be rewritten using set-based operations in which case the loops are performed inside the query plan and since they involve no context switch are much faster.
However, there are some things SQL Server
is just not good at, like computing cumulative values or joining on date ranges.
These kinds of queries can be made faster using a CURSOR
:
But again, this is a quite a rare exception, and normally a set-based way performs better.
If you posted your query, we could probably optimize it and get rid of a CURSOR
.
Related videos on Youtube
goofyui
Programmer - Interested in Big Data, Statistical Data Analysis, Cloud Computing, Mobile Apps
Updated on August 02, 2020Comments
-
goofyui over 3 years
Using SQL 2005 / 2008
I have to use a forward cursor, but I don't want to suffer poor performance. Is there a faster way I can loop without using cursors?
-
Oded about 13 yearsIt entirely depends on what you are trying to do! Perhaps give us a hint, so we can give you an answer that will make sense to your situation?
-
Oded about 13 yearsI am talking about the specific task that you think you need cursors for. Explain what that is and a helpful answer may be given.
-
-
goofyui about 13 yearsNeil, Thank you ..! I believe you are right. Can you please some syntax as Sample.
-
Neil Knight about 13 yearsThere are examples on the link :o)
-
DaveE about 13 yearsI think @Dustin is saying that if you can do this using set-based operations, the looping might be entirely unnecessary.
-
goofyui about 13 yearsand @DavE, you both might be right. What exactly you are trying to explain by Set Operators? I am sure that Recursive queries can do it ..!
-
goofyui about 13 yearssqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx - I found an article about replacing cursors by while loop .. ! As you /@Dave mentioned. Again, here we are creating a Temp Table ..!
-
Dustin Laine about 13 years@Chok - You are 100% correct that a
WHILE
loop is an integral part of aCURSOR
. However, I have seenWHILE
loops perform better that aCURSOR
as a cursor has a lot of options that can hinder performance. I believe aFAST FORWARD
CURSOR
should be similar to a regularWHILE
loop. -
Solomon Rutzky about 13 yearsI am not necessarily saying to create a temp table. I am saying that using the STATIC keyword will store the results of the Cursor query in a Temp Table automatically and hence not lock any of the tables in the query. It would be worth exploring this option of simply adding a single word "STATIC" to your code to see if you get the desired performance gain before re-engineering the code to have a WHILE loop.
-
Solomon Rutzky over 4 years-1 Link-only answers are frowned upon since the external resource is not guaranteed to exist. Please copy relevant content from that link into this answer, and then I will remove the -1. Thanks.
-
AjV Jsy over 4 yearsWell explained case - the link is dead