SQL server cursor slow performance

Question

I'm getting started with my first use of a cursor in a stored procedure in sql server 2008. I've done some preliminary reading and I understand that they have significant performance limitations. In my current case I think they're necessary (I want to run multiple stored procedures for each stock symbol in a symbols table.

Edit: The sprocs I'll be calling on each symbol will for the most part be insert operations to calculate symbol- dependent values, such as 5 day moving average, average daily volume, ATR (average true range). Most of these values will be calculated from data from a daily pricing and volume table... I'd like to streamline the retrieval of data values that would be retrieved redundantly otherwise... for example, I'd like to get for each symbol the daily pricing and volume data into a table variable... that temp table will then be passed in to the stored procedure that calls each of the aggregated functions I just mentioned. Hope that makes sense...

So my initial "outer loop" cursor- based stored procedure is below.. it times out after several minutes, without returning anything to the output window.

ALTER PROCEDURE dbo.sprocSymbolDependentAggsDriver2

    AS

    DECLARE @symbol nchar(10)
    DECLARE symbolCursor CURSOR
    STATIC FOR
    SELECT Symbol FROM tblSymbolsMain ORDER BY Symbol

    OPEN symbolCursor
    FETCH NEXT FROM symbolCursor INTO @symbol
    WHILE @@FETCH_STATUS = 0
        SET @symbol = @symbol + ': Test.'
        FETCH NEXT FROM symbolCursor INTO @symbol

    CLOSE symbolCursor
    DEALLOCATE symbolCursor

When I run it without the @symbol local variable and eliminate the assignment to it in the while loop, it seems to run ok. Is there a clear violation of performance best- practices within that assignment? Thanks..

Answer 1

"In my current case I think they're necessary (I want to run multiple stored procedures for each stock symbol in a symbols table."

Cursors are rarely necessary.

From your example above, I think a simple WHILE loop will easily take the place of your cursor. Adapted from SQL Cursors - How to avoid them (one of my favorite SQL bookmarks)

-- Create a temporary table...
CREATE TABLE #Symbols (
 RowID int IDENTITY(1, 1), 
 Symbol(nvarchar(max))
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @Symbol nvarchar(max)

-- Get your data that you want to loop over
INSERT INTO #Symbols (Symbol)
SELECT Symbol
FROM tblSymbolsMain 
ORDER BY Symbol

-- Get the number of records you just grabbed
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1


-- Just do a WHILE loop.  No cursor necessary.
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @Symbol = Symbol
 FROM #Symbols
 WHERE RowID = @RowCount

 EXEC <myProc1> @Symbol
 EXEC <myProc2> @Symbol
 EXEC <myProc3> @Symbol

 SET @RowCount = @RowCount + 1
END


DROP TABLE #Symbols

Answer 2

You don't really need all that explicit cursor jazz to build a string. Here is probably a more efficient way to do it:

DECLARE @symbol NVARCHAR(MAX) = N'';

SELECT @symbol += ': Test.'
    FROM dbo.tblSymbolsMain 
    ORDER BY Symbol;

Though I suspect you actually wanted to see the names of the symbol, e.g.

DECLARE @symbol NVARCHAR(MAX) = N'';

SELECT @symbol += N':' + Symbol
    FROM dbo.tblSymbolsMain 
    ORDER BY Symbol;

One caveat is that while you will typically observe the order to be observed, it is not guaranteed. So if you want to stick to the cursor, at least declare the cursor as follows:

DECLARE symbolCursor CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
...

Also it seems to me like NCHAR(10) is not sufficient to hold the data you're trying to stuff into it, unless you only have one row (which is why I chose NVARCHAR(MAX) above).

And I agree with Abe... it is quite possible you don't need to fire a stored procedure for every row in the cursor, but to suggest ways around that (which will almost certainly be more efficient), we'd have to understand what those stored procedures actually do.