What is an alternative to cursors for sql looping?

问题:

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?

回答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

回答2:

Recursive Queries using Common Table Expressions.

回答3:

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

回答4:

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:

• Flattening timespans: SQL Server

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.

回答5:

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.

First option is to try to find a set-based approach to the problem.

If logically there is no set-based approach, 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 a Temp Table and hence will not lock the base-tables of the query as you iterate through the results. 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).

回答6:

Depending on what you want it for, you may be able to use a tally table.

Jeff Moden has an excellent article on tally tables Here

回答7:

Don't use a cursor, instead look for a set-based solution. If you can't find a set-based solution... still don't use a cursor! Post details of what you are trying to achieve, someone will be able to find a set-based solution for you.

回答8:

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

回答9:

There may be some scenarios where one can use Tally tables. It could be a good alternative of loop and cusrors but remember it cannot be applied in every case. A well explain case can be found here