- \n
- What are cursors for?<\/a><\/li>\n
- Where would you use a cursor?<\/a><\/li>\n
- Global cursors<\/a><\/li>\n
- Are Cursors Slow?<\/a><\/li>\n
- Cursor Variables<\/a><\/li>\n
- Cursor Optimisation<\/a><\/li>\n
- Questions<\/a><\/li>\n
- Acknowledgements<\/a><\/li>\n<\/ul>\n
What are cursors for?<\/h2>\n
Cursors were created to bridge the ‘impedence mismatch’ between the ‘record- based’ culture of conventional programming and the set-based world of the relational database.<\/p>\n
They had a useful purpose in allowing existing applications to change from ISAM or KSAM databases, such as DBaseII, to SQL Server with the minimum of upheaval. DBLIB and ODBC make extensive use of them to ‘spoof’ simple file-based data sources.<\/p>\n
Relational database programmers won’t need them but, if you have an application that understands only the process of iterating through resultsets, like flicking through a card index, then you’ll probably need a cursor.<\/p>\n
Where would you use a Cursor?<\/h2>\n
An simple example of an application for which cursors can provide a good solution is one that requires running totals. A cumulative graph of monthly sales to date is a good example, as is a cashbook with a running balance.<\/p>\n
We’ll try four different approaches to getting a running total…<\/p>\n
\/*so let's build a very simple cashbook *\/ \r\n CREATE TABLE #cb ( cb_ID INT IDENTITY(1,1),--sequence of entries 1..n \r\n Et VARCHAR(10), --entryType \r\n amount money)--quantity \r\n INSERT INTO #cb(et,amount) SELECT 'balance',465.00 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',56.00 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',434.30 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',20.04 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',65.00 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',23.22 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',45.80 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',34.08 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',78.30 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',56.00 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',75.22 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',5.80 \r\n INSERT INTO #cb(et,amount) SELECT 'purchase',3.08 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',3.29 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',100.80 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',100.22 \r\n INSERT INTO #cb(et,amount) SELECT 'sale',23.80 \r\n \r\n \/* You don't actually need a cursor. You can get a running total using a correlated subquery *\/ \r\n SELECT [Entry Type]=Et, amount, \r\n [balance after transaction]=( \r\n SELECT SUM(--the correlated subquery \r\n CASE WHEN total.Et='purchase' \r\n THEN -total.amount \r\n ELSE total.amount \r\n END) \r\n FROM #cb total WHERE total.cb_id <= #cb.cb_id ) \r\n FROM #cb ORDER BY #cb.cb_id \r\n \r\n --or you can do this simple inner join and group-by clause if you don't \r\n --like correlated subqueries \r\n SELECT [Entry Type]=MIN(#cb.Et), [amount]=MIN (#cb.amount), \r\n [balance after transaction]= \r\n SUM(CASE WHEN total.Et='purchase' \r\n THEN -total.amount \r\n ELSE total.amount \r\n END) \r\n FROM #cb total INNER JOIN #cb ON total.cb_id <= #cb.cb_id \r\n GROUP BY #cb.cb_id ORDER BY #cb.cb_id \r\n \r\n --and here is a very different technique that takes advantege \r\n --of the quirky behavionr of SET in an UPDATE command in SQL Server \r\n \r\n DECLARE @cb TABLE(cb_ID INT,--sequence of entries 1..n \r\n Et VARCHAR(10), --entryType \r\n amount money,--quantity \r\n total money) \r\n DECLARE @total money \r\n SET @total = 0 \r\n \r\n INSERT INTO @cb(cb_id,Et,amount,total) \r\n SELECT cb_id,Et,CASE WHEN Et='purchase' \r\n THEN -amount \r\n ELSE amount \r\n END,0 FROM #cb order by cb_id\r\n UPDATE @cb \r\n SET @total = total = @total + amount FROM @cb \r\n SELECT [Entry Type]=Et, [amount]=amount, \r\n [balance after transaction]=total FROM @cb ORDER BY cb_id \r\n \r\n \r\n -- or you can give up trying to do it a set-based way and \r\n -- iterate through the table \r\n \r\n DECLARE @ii INT, @iiMax INT, @CurrentBalance money \r\n DECLARE @Runningtotals TABLE (cb_id INT, Total money) \r\n SELECT @ii=MIN(cb_id), @iiMax=MAX(cb_id),@CurrentBalance=0 FROM #cb \r\n \r\n WHILE @ii<=@iiMax \r\n BEGIN \r\n SELECT @currentBalance=@currentBalance \r\n +CASE WHEN Et='purchase' \r\n THEN -amount \r\n ELSE amount \r\n END FROM #cb WHERE cb_ID=@ii \r\n INSERT INTO @runningTotals(cb_id, Total) SELECT @ii,@currentBalance \r\n SELECT @ii=@ii+1 \r\n END \r\n SELECT[Entry Type]=Et,amount,total \r\n FROM #cb INNER JOIN @Runningtotals r ON #cb.cb_id=r.cb_id \r\n \r\n \/* \r\n or alternatively you can use...... \r\n ----------....A CURSOR!!! \r\n the use of a cursor will normally involve a DECLARE, OPEN, several \r\n FETCHs, a CLOSE and a DEALLOCATE \r\n *\/ \r\n SET Nocount ON \r\n DECLARE @Runningtotals TABLE (cb_id INT, Et VARCHAR(10), --entryType \r\n amount money, Total money) \r\n DECLARE @CurrentBalance money, @Et VARCHAR(10), @amount money \r\n --Declare the cursor \r\n --declare current_line cursor -- SQL-92 syntax--only scroll forward \r\n \r\n DECLARE current_line CURSOR fast_forward--SQL Server only--only scroll forward \r\n FOR \r\n SELECT Et,amount \r\n FROM #cb ORDER BY cb_id \r\n FOR READ ONLY \r\n --now we open the cursor to populate any temporary tables (in the case of \r\n -- cursors) etc.. \r\n --Cursors are unusual because they can be made GLOBAL to the connection. \r\n OPEN current_line \r\n --fetch the first row \r\n FETCH NEXT FROM current_line \r\n INTO @Et,@amount \r\n WHILE @@FETCH_STATUS = 0--whilst all is well \r\n BEGIN \r\n SELECT @CurrentBalance = COALESCE(@CurrentBalance,0) \r\n +CASE WHEN @Etyle=\"COLOR: blue\">='purchase' \r\n THEN -@amount \r\n ELSE @amount \r\n END \r\n INSERT INTO @Runningtotals (Et, amount,Total) \r\n SELECT @Et,@Amount,@CurrentBalance \r\n -- This is executed as long as the previous fetch succeeds. \r\n FETCH NEXT FROM current_line \r\n INTO @Et,@amount \r\n END \r\n SELECT [Entry Type]=Et,amount,Total FROM @Runningtotals ORDER BY cb_id \r\n CLOSE current_line--Do not forget to close when its result set is not needed. \r\n --especially a global updateable cursor! \r\n DEALLOCATE current_line \r\n \r\n -- although the Cursor code looks bulky and complex, on small tables it will \r\n -- execute just as quickly as a simple iteration, and will be faster with tables \r\n -- of any size if you forget to put an index on the table through which you're \r\n -- iterating! \r\n \r\n -- The first two solutions are faster with small tables but slow down \r\n -- exponentially as the table size grows. \r\n \r\n \/* here is the result of all the routines above \r\n Entry Type amount balance after transaction \r\n ---------- --------------------- ------------------------- \r\n balance 465.00 465.00 \r\n sale 56.00 521.00 \r\n sale 434.30 955.30 \r\n purchase 20.04 935.26 \r\n purchase 65.00 870.26 \r\n sale 23.22 893.48 \r\n sale 45.80 939.28 \r\n purchase 34.08 905.20 \r\n purchase 78.30 826.90 \r\n purchase 56.00 770.90 \r\n sale 75.22 846.12 \r\n sale 5.80 851.92 \r\n purchase 3.08 848.84 \r\n sale 3.29 852.13 \r\n sale 100.80 952.93 \r\n sale 100.22 1053.15 \r\n sale 23.80 1076.95 \r\n *\/ \r\n <\/pre>\n
Why not try these different approaches, with tables of different sizes and see how long the routines take? (I demonstrate a suitable test-rig shortly).<\/p>\n
Is there a quicker or more elegant solution?<\/p>\n
Global Cursors<\/h2>\n
If you are doing something really complicated with a listbox, or scrolling through a rapidly-changing table whilst making updates, a GLOBAL cursor could be a good solution, but is is very much geared for traditional client-server applications, because cursors have a lifetime only of the connection. Each ‘client’ therefore needs their own connection. The
GLOBAL<\/code> cursors defined in a connection will be implicitly deallocated at disconnect.<\/p>\nGlobal Cursors can be passed too and from stored procedure and referenced in triggers. They can be assigned to local variables. A global cursor can therefore be passed as a parameter to a number of stored procedures Here is an example, though one is struggling to think of anything useful in a short example.<\/p>\n
CREATE PROCEDURE spReturnEmployee ( \r\n @EmployeeLastName VARCHAR(20), \r\n @MyGlobalcursor CURSOR VARYING OUTPUT \r\n ) \r\n AS \r\n BEGIN \r\n SET NOCOUNT ON \r\n SET @MyGlobalcursor = CURSOR STATIC FOR \r\n SELECT lname, fname FROM pubs.dbo.employee \r\n WHERE lname = @EmployeeLastName \r\n OPEN @MyGlobalcursor \r\n END \r\n \r\n . \r\n DECLARE @FoundEmployee CURSOR, \r\n @LastName VARCHAR(20), \r\n @FirstName VARCHAR(20) \r\n EXECUTE spReturnEmployee 'Lebihan', @FoundEmployee OUTPUT \r\n --see if anything was found \r\n --note we are careful to check the right cursor! \r\n IF CURSOR_STATUS('variable', '@FoundEmployee') = 0 \r\n SELECT 'no such employee' \r\n ELSE \r\n BEGIN \r\n FETCH NEXT FROM @FoundEmployee INTO @LastName, @FirstName \r\n SELECT @FirstName+' '+@LastName \r\n END \r\n CLOSE @FoundEmployee \r\n DEALLOCATE @FoundEmployee \r\n <\/pre>\nTransact-SQL cursors are efficient when contained in stored procedures and triggers. This is because everything is compiled into one execution plan on the server and there is no overhead of network traffic whilst fetching rows.<\/p>\n
Are Cursors Slow?<\/h2>\n
So what really are the performance differences? Let’s set up a test-rig. We’ll give each routine an increasingly big cashbook to work on up to 2 million rows, and give it a task that doesn’t disturb SSMS\/Query analyser too much with a large result, so we can measure just the performance of each algorithm. We’ll put the timings into a table that we can put into excel and run a pivot on to do the analysis.<\/p>\n
We’ll calculate the average balance, and the highest and lowest balance so as to check that the results of each method agree.<\/p>\n
Now, which solution is going to be the best?<\/p>\n
------------------------------------------------------------------------\r\n -- Test harness\r\n ------------------------------------------------------------------------\r\n --declare the local variables\r\n DECLARE @ii INT, @iiMax INT, @CurrentBalance MONEY \r\n DECLARE @Et VARCHAR(10), @amount MONEY \r\n \r\n --and clean up the harness from the last run. \r\n IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLE_NAME LIKE '#Events[_]%') DROP TABLE #Events\r\n IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLE_NAME LIKE '#TableSizes[_]%') DROP TABLE #TableSizes\r\n IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLE_NAME LIKE '#TempCB[_]%') DROP TABLE #TempCB\r\n IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLE_NAME LIKE '#RunningTotals[_]%') DROP TABLE #RunningTotals\r\n \r\n --Firstly, we'll make a table to record our results for each table size\r\n CREATE TABLE #Events (Event_ID INT IDENTITY(1,1),--sequence of Events 1..n \r\n [Event] VARCHAR(50) NOT NULL, --The Event we're recording\r\n [method] VARCHAR(50) NOT NULL, --the algorithm we are using\r\n TableSize INT NOT NULL, \r\n Time DATETIME DEFAULT GETDATE())--The moment that it happened\r\n \r\n CREATE TABLE #tempcb (cb_ID INT,--sequence of entries 1..n \r\n Et VARCHAR(10), --entryType \r\n amount MONEY,--quantity \r\n total MONEY) \r\n DECLARE @total MONEY \r\n \r\n CREATE TABLE #Runningtotals (cb_id INT, Total MONEY) \r\n \r\n \r\n --now, we'll have a table of the table sizes that we want\r\n CREATE TABLE #TableSizes (TableSize_ID INT IDENTITY(1,1),--TableSizes to try out with\r\n TableSize INT NOT NULL\r\n )\r\n --and fill them with the table sizes we'll be using. (Change to taste)\r\n INSERT INTO #tablesizes (TableSize) \r\n SELECT 20 UNION SELECT 200 UNION SELECT 2000 UNION \r\n SELECT 20000 union select 200000 union select 2000000\r\n \r\n DECLARE @tablesizeRow INT,@maxTableSizeRow INT,@TableSize INT\r\n SELECT @tablesizeRow = MIN(TableSize_ID),\r\n @maxTableSizeRow = MAX(TableSize_ID)\r\n FROM #Tablesizes\r\n \r\n WHILE @tablesizeRow <= @maxTableSizeRow\r\n BEGIN\r\n --firstly, get the number of rows\r\n SELECT @TableSize = tablesize\r\n FROM #tablesizes\r\n WHERE TableSize_ID = @TablesizeRow\r\n SELECT @TablesizeRow = @TablesizeRow + 1 \r\n --Delete the cashbook! \r\n IF EXISTS \r\n ( SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLE_NAME LIKE '#cb[_]%' ) DROP TABLE #cb \r\n --create a new randomly-generated cashbook table.\r\n CREATE TABLE #cb\r\n (\r\n cb_ID INT IDENTITY(1, 1),\r\n Et VARCHAR(10), --entryType \r\n amount MONEY\r\n )--quantity \r\n INSERT INTO #cb\r\n (et, amount) SELECT 'balance', 465.00 \r\n SELECT @ii = 0 \r\n WHILE @ii <= @TableSize \r\n BEGIN \r\n INSERT INTO #cb (et, amount)\r\n SELECT CASE WHEN RAND() < 0.5 THEN 'sale'\r\n ELSE 'purchase'\r\n END, CAST(RAND() * 180.00 AS MONEY) \r\n SELECT @ii = @ii + 1 \r\n END \r\n --and put an index on it \r\n CREATE CLUSTERED INDEX idxcbid ON #cb (cb_id) \r\n CREATE INDEX covering ON #cb (cb_id, et, amount)\r\n \r\n --first try the correlated subquery approach... \r\n IF @TableSize < 200000 --they run out of steam pretty soon.\r\n BEGIN\r\n INSERT INTO #Events (method, event, tablesize)\r\n SELECT 'Correlated Subquery', 'start', @TableSize\r\n \r\n SELECT 'correlated subquery', MIN(g.balance),\r\n AVG(g.balance), MAX(g.balance)\r\n FROM (SELECT [balance] = \r\n (SELECT SUM(--the correlated subquery \r\n CASE WHEN total.Et = 'purchase' THEN -total.amount\r\n ELSE total.amount\r\n END)\r\n FROM #cb total WHERE total.cb_id <= #cb.cb_id\r\n )\r\n FROM #cb) g \r\n \r\n --then we'll do the 'group by and inner join'\r\n INSERT INTO #Events\r\n (method, event, tablesize) SELECT 'group by', 'start', @TableSize\r\n \r\n SELECT 'Group by...', MIN(f.balance), AVG(f.balance), MAX(f.balance)\r\n FROM (SELECT \r\n [balance] = SUM(CASE WHEN total.Et = 'purchase' \r\n THEN -total.amount ELSE total.amount\r\n END)\r\n FROM #cb total INNER JOIN #cb ON total.cb_id <= #cb.cb_id\r\n GROUP BY #cb.cb_id\r\n ) f\r\n END ---end of the slow section\r\n \r\n -- Now let's try the \"quirky\" technique using SET \r\n INSERT INTO #Events (method, event, tablesize)\r\n SELECT 'Quirky Update', 'start', @TableSize\r\n \r\n SET @total = 0 \r\n TRUNCATE TABLE #TEMPCB\r\n INSERT INTO #TempCb\r\n (cb_id, Et, amount, total)\r\n SELECT cb_id, Et,\r\n CASE WHEN Et = 'purchase' THEN -amount\r\n ELSE amount END, 0\r\n FROM #cb order by cb_id\r\n UPDATE #TempCb\r\n SET @total = total = @total + amount\r\n SELECT 'quirky Update', MIN(Total), AVG(Total), MAX(Total)\r\n FROM #TempCb\r\n -- now the simple iterative solution \r\n \r\n INSERT INTO #Events (method, event, tablesize)\r\n SELECT 'Iterative Solution', 'start',@TableSize\r\n \r\n SELECT @ii = MIN(cb_id), @iiMax = MAX(cb_id),\r\n @CurrentBalance = 0\r\n FROM #cb \r\n TRUNCATE TABLE #RunningTotals\r\n WHILE @ii <= @iiMax \r\n BEGIN \r\n SELECT @currentBalance = @currentBalance \r\n + CASE WHEN Et = 'purchase' THEN -amount ELSE amount END\r\n FROM #cb WHERE cb_ID = @ii \r\n INSERT INTO #runningTotals (cb_id, Total)\r\n SELECT @ii, @currentBalance \r\n SELECT @ii = @ii + 1 \r\n END \r\n SELECT 'iterative method', MIN(Total), AVG(Total), MAX(Total)\r\n FROM #Runningtotals \r\n -- now the simple iterative solution \r\n INSERT INTO #Events (method, event, tablesize)\r\n SELECT 'Cursor Solution', 'start', @TableSize\r\n \r\n --Declare the cursor \r\n --declare current_line cursor -- SQL-92 syntax \r\n ---scroll forward only \r\n \r\n DECLARE current_line CURSOR fast_forward--SQL Server only \r\n ---scroll forward \r\n FOR SELECT cb_id, Et, amount\r\n FROM #cb\r\n ORDER BY cb_id\r\n FOR READ ONLY \r\n TRUNCATE TABLE #Runningtotals\r\n --now we open the cursor to populate any temporary tables \r\n OPEN current_line \r\n --fetch the first row \r\n FETCH NEXT FROM current_line INTO @ii, @Et, @amount \r\n SELECT @currentBalance = 0\r\n WHILE @@FETCH_STATUS = 0--whilst all is well \r\n BEGIN \r\n SELECT @CurrentBalance = COALESCE(@CurrentBalance,0)\r\n + CASE WHEN @Et = 'purchase' \r\n THEN -@amount ELSE @amount END \r\n INSERT INTO #runningTotals (cb_id, Total)\r\n SELECT @ii, @currentBalance \r\n -- This is executed as long as the previous fetch succeeds. \r\n FETCH NEXT FROM current_line INTO @ii, @Et, @amount \r\n END \r\n \r\n CLOSE current_line--Do not forget to close \r\n DEALLOCATE current_line \r\n SELECT 'cursor method', MIN(Total), AVG(Total), MAX(Total)\r\n FROM #Runningtotals\r\n INSERT INTO #Events (method, event, tablesize)\r\n SELECT 'Test Run', 'End', 0\r\n END\r\n --Now it is all done, get a report of the findings\r\n SELECT method, Tablesize,\r\n DATEDIFF(ms, Time,\r\n (SELECT time FROM #events [next] WHERE next\r\n . event_ID = this . Event_ID + 1\r\n ) ) [Duration(ms)]\r\n FROM #Events AS [this] WHERE event = 'start'\r\n \r\n ------------------------------------------------------------------------\r\n -- End of Test harness\r\n ------------------------------------------------------------------------\r\n <\/pre>\n
![\"339-QuirkyData.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/339-QuirkyData.jpg\")
<\/p>\nThe raw data is shown here. What I have not shown is the check of the results, which shows that every solution gave consistent results.<\/p>\n
The iterative and cursor solution both give similar results since, under the covers, they are doing similar things. They are dramatically faster than the ‘correlated subquery’ and ‘group by’ methods as one would expect.<\/p>\n
You will see from the graph that we couldn’t even attempt the correlated subquery methods under a ‘production’ table size. It would have taken too long.<\/p>\n
- Where would you use a cursor?<\/a><\/li>\n
![\"339-QuirkyGraph.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/339-QuirkyGraph.jpg\")
<\/p>\n