Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

 

Cursors

 

Cursors are record oriented; only one row can be read or updated at a time. There is one request to SQL Server for every row processed which is inherently slow. When dealing with a high number of rows or when the cursorís properties are poorly choosen, cursors can cause  severe performance degradation. Cursors are not the best choice when working with a high number of rows because of the sequential access to the database, excessive locking and the amount of overhead required by the cursor arguments (static, forward-only, read-only, etc.).
 

That is why cursors can be very slow and a high number of rows will make it worse. Choosing the arguments when opening a cursor is essential in terms of performance and depends on each particular situation.
 

SELECT, UPDATE, DELETE and INSERT statements are set oriented and so changes happen on a group of rows, much faster than with cursors. A rule of thumb is that if the same result can be obtained with a cursor and a set oriented statement, the latter should be chosen. There are exceptions, one is when the rows are to be selected by their ordinal location and there is no direct way to determine it and the other is when recursion would be necessary.

 

Example: Selecting the first two company names (sorted alphabetically) per country from table Customers. This is an example of when a correlated subquery would be the best choice.

 

SELECT CompanyName, Country FROM Customers c1

WHERE CompanyName+Country IN

      (SELECT TOP 2 CompanyName+Country

      FROM Customers c2

      WHERE c1.Country=c2.Country

      ORDER BY Country, CompanyName)

ORDER BY Country, CompanyName

 

This is the same task performed with a cursor:

 

DECLARE @tmpTable table (CompanyName NVARCHAR(100), Country NVARCHAR(50))

DECLARE @CompanyName NVARCHAR(100), @Country NVARCHAR(50), @CountryOld NVARCHAR(50), @Counter int

SET @Counter=0

DECLARE cur_sample1 CURSOR FOR

SELECT CompanyName, Country FROM Customers ORDER BY Country, CompanyName

OPEN cur_sample1

FETCH NEXT FROM cur_sample1 INTO @CompanyName, @Country

WHILE @@FETCH_STATUS = 0

      BEGIN

      IF @Country=@CountryOld

            SET @Counter=@Counter+1

      ELSE

      BEGIN

      SET @Counter=0

      SET @CountryOld=@Country

      END

      IF @Counter<2

            INSERT INTO @tmpTable VALUES (@CompanyName, @Country)

      FETCH NEXT FROM cur_sample1 INTO @CompanyName, @Country

      END

CLOSE cur_sample1

DEALLOCATE cur_sample1

SELECT CompanyName, Country FROM @tmpTable ORDER BY Country, CompanyName

 

Besides being more complex, the performance is also much inferior. In fact, the performance of other code might be affected too. If Views or SPís are delayed because of rows locked by the cursor, then the overall performance could suffer.

 

Let us consider now cases that cannot be solved with SELECT statements because the filtering is based on the rowís ordinal position.

 

Example: Select one row out of every four from table Customers.

 

This can be done without cursors; a temporary table with an extra column (id) for the ordinal created with the IDENTITY function would work fine. Then all records from the temporary table with an id different from one or not divisible by four would be selected.

 

DECLARE @tmpTable1 table (mID int IDENTITY , CompanyName NVARCHAR(100), Country NVARCHAR(50))

INSERT INTO @tmpTable1(CompanyName, Country)

SELECT CompanyName, Country

FROM Customers ORDER BY Country, CompanyName

SELECT CompanyName, Country FROM @tmpTable1

WHERE (mID=1) or (((mID-1) % 4)=0)

ORDER BY Country, CompanyName

 

Cursors can be scrollable, being able to move to any row by its ordinal position, either absolute or relative. The SCROLL clause defines a cursor as scrollable. While a cursor normally works sequentially, fetching one row at a time, a scrollable cursor can fetch any row by its location.

 

DECLARE @tmpTable table (CompanyName NVARCHAR(100), Country NVARCHAR(50))

DECLARE @CompanyName NVARCHAR(100), @Country NVARCHAR(50)

DECLARE cur_sample1 SCROLL CURSOR FOR

SELECT CompanyName, Country FROM Customers ORDER BY Country, CompanyName

OPEN cur_sample1

FETCH NEXT FROM cur_sample1 INTO @CompanyName, @Country

WHILE @@FETCH_STATUS = 0

      BEGIN

      INSERT INTO @tmpTable VALUES (@CompanyName, @Country)

      FETCH RELATIVE 4 FROM cur_sample1 INTO @CompanyName, @Country

      END

CLOSE cur_sample1

DEALLOCATE cur_sample1

SELECT CompanyName, Country FROM @tmpTable ORDER BY Country, CompanyName

 

In this example the temporary table solution should be faster than the cursor because it was possible to use a simple formula to filter the desired rows from the temporary table, even with the overhead of creating the temporary table. What if the formula is not that simple?

 

Example: Select the first row from table Customers, then the second, third, fifth, eighth... That is, selecting rows by their location, following a Fibonacci sequence: 0, 1, 1, 2, 3, 5, 8, 13... However, ignoring the first two elements, zero and one.

 

SQL Server has many mathematical functions but none for determining if a number is in a Fibonacci sequence. If it was a simple formula, it could be placed with the WHERE clause but this is not the case.

 

CREATE function isFIBONACCI(@intInput int)

--Returns whether a given number is in a Fibonacci sequence.

returns bit

as

BEGIN

      DECLARE @Fib int, @PrevFib int, @tmpFib int, @Counter int, @temp bit

      SET @Fib=1

      SET @PrevFib=1

      SET @Counter=1

      WHILE (@Fib<@intInput)

            BEGIN

            SET @tmpFib=@PrevFib

            SET @PrevFib=@Fib

            SET @Fib=@Fib+@tmpFib

            SET @Counter=@Counter+1

            END

      IF @Fib=@intInput

            SET @temp=1

      ELSE

            SET @temp=0

      return @temp

END

 

Now that the function is ready it is very simple to use it:

 

DECLARE @tmpTable1 table (mID int IDENTITY , CompanyName NVARCHAR(100), Country NVARCHAR(50))

INSERT INTO @tmpTable1(CompanyName, Country)

SELECT CompanyName, Country

FROM Customers ORDER BY Country, CompanyName

SELECT CompanyName, Country FROM @tmpTable1

WHERE (dbo.isFIBONACCI(mID)=1)

ORDER BY Country, CompanyName

 

The changes were minimal, only the WHERE clause had to be modified. The cursor code also had a few changes:

 

DECLARE @tmpTable table (CompanyName NVARCHAR(100), Country NVARCHAR(50))

DECLARE @CompanyName NVARCHAR(100), @Country NVARCHAR(50), @itmp int, @ordinal int, @PrevOrdinal int

SET @ordinal=1

SET @PrevOrdinal=1

DECLARE cur_sample1 SCROLL CURSOR FOR

SELECT CompanyName, Country FROM Customers ORDER BY Country, CompanyName

OPEN cur_sample1

FETCH NEXT FROM cur_sample1 INTO @CompanyName, @Country

SEE CODE DEPOT

      SET @itmp=@PrevOrdinal

      SET @PrevOrdinal=@ordinal

      SET @ordinal=@ordinal+@itmp

      END

CLOSE cur_sample1

DEALLOCATE cur_sample1

SELECT CompanyName, Country FROM @tmpTable ORDER BY Country, CompanyName

 

The ABSOLUTE clause fetches rows by their absolute location:

 

FETCH ABSOLUTE @ordinal FROM cur_sample1 INTO @CompanyName, @Country

 

In this particular case RELATIVE would work by moving from the current row position by the value of the increment from the Fibonacci sequence:

 

FETCH RELATIVE @PrevOrdinal FROM cur_sample1 INTO @CompanyName, @Country

 

The solution with a temporary table and a UDF is still faster than a cursor. The factors influencing the difference in speed are:

* The cursor is filling a temporary table for output. If there were no rows to be returned but, instead, they were to be updated directly in table Customers then the cursor would do better. Not that the cursor would run faster because it would still have one update replacing the one insert but the other solution would have to store the CustomerIDís in the temporary table and used them for the update. It would even out
.

* A SCROLL cursor performance is better with a huge amount of rows and few fetches.
 

* The formula in the UDF had the same level of complexity as in the cursor. The overhead of using a UDF was the main cause of loss of performance for the solution based on a temporary table.

A SCROLL cursor is definitively the best solution for finding the mode from a high number of rows. It is also the best way to get the nth element or any other similar problem that needs very few fetches.

 

There is another type of problem that only a SCROLL cursor can solve: when the next row position is calculated not by a formula but from a value from the current row. This is similar to the problem, with a regular cursor, of calculating a total in a row that would include a value from the previous row. With a SCROLL cursor, there are more possibilities for creative solutions.

 

Let us now examine regular cursors which can only move one row at a time either forward or backwards.

 

Example: To calculate the total of all orders by month and the sum of all months until the current one, also included. There is already a solution in the viewís section using myMonthlyCostSum but this solution with a cursor is actually faster than using a View.

 

First attempt with an updatable cursor.

 

CREATE PROCEDURE myspMonthlyCostSumCur

as

--Using Cursor

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

insert into @tmpTable

select YEAR(dbo.Orders.OrderDate) , MONTH(dbo.Orders.OrderDate) ,

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)),0

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

DECLARE @order_year int, @order_month int, @total money, @totalsum money

SET @totalsum=0

DECLARE cur_med  CURSOR

FOR select order_year, order_month, total from @tmpTable

FOR UPDATE OF totalsum

OPEN cur_med

FETCH NEXT FROM cur_med INTO @order_year, @order_month, @total

WHILE @@FETCH_STATUS = 0

     

SEE CODE DEPOT

CLOSE cur_med

DEALLOCATE cur_med

select order_year, order_month, total, totalsum from @tmpTable

GO

 

There is room for improvement, for example, the temporary table is first populated with an INSERT statement and then there is an UPDATE that will work on all rows, one at a time. By using only one INSERT statement inside the cursor, the INSERT/UPDATE is no longer necessary. Another advantage is that because the cursor does not need to be updatable anymore, it can be READ_ONLY because there are no data changes and as it will move forward only, it can be FORWARD_ONLY. Those advantages will improve performance, even without using indexes.
 

Here is the improved code:

 

CREATE PROCEDURE myspMonthlyCostSumCur2

as

--Using Cursor

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

DECLARE @order_year int, @order_month int, @total money, @totalsum money

SET @totalsum=0

DECLARE cur_med  CURSOR FORWARD_ONLY READ_ONLY   FOR

select YEAR(dbo.Orders.OrderDate) , MONTH(dbo.Orders.OrderDate) ,

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount))

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

OPEN cur_med

FETCH NEXT FROM cur_med INTO @order_year, @order_month, @total

WHILE @@FETCH_STATUS = 0

     SEE CODE DEPOT

CLOSE cur_med

DEALLOCATE cur_med

select order_year, order_month, total, totalsum from @tmpTable

GO

 

There are other solutions without cursors or correlated subqueries, after the success of using temporary tables with cursors, it seems tempting to try a temporary table without a cursor. After the temporary table is created and all the hard work of grouping and summing, the final summation could be accomplished with a CROSS JOIN.
 

Here is the code:

 

CREATE PROCEDURE myspMonthlyCostSumTemp

as

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

insert into @tmpTable

select YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate),

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)),0

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

SELECT t1.order_year, t1.order_month, sum(t2.total) as total_sum

SEE CODE DEPOT

GROUP BY t1.order_year, t1.order_month

ORDER BY t1.order_year, t1.order_month

GO

 

Using CROSS JOIN is a very bad idea in the real world because the results might have an excessive number of rows. On the other hand, using a correlated subquery with a temporary table could improve performance:

 

CREATE PROCEDURE myspMonthlyCostSumTemp2

as

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

insert into @tmpTable

select YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate),

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)),0

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

SELECT order_year, order_month, (

SELECT SUM(total) FROM @tmpTable t2

SEE CODE DEPOT

FROM @tmpTable t1

GROUP BY order_year, order_month

ORDER BY order_year, order_month

GO

 

There are many articles condemning cursors and sometimes strongly recommending using a WHILE loop instead of a cursor. The next example uses that method:

 

CREATE PROCEDURE myspMonthlyCostSumCurAlt

as

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

insert into @tmpTable

select YEAR(dbo.Orders.OrderDate) , MONTH(dbo.Orders.OrderDate) ,

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)),0

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

DECLARE @Counter int, @NumRecords int, @order_year int, @order_month int, @total money, @totalsum money

, @Nmonths int, @tmp int

SET @total=0

SET @totalsum=0

SET @Counter=1

SET @Nmonths=0

SELECT @NumRecords=COUNT(*) FROM @tmpTable

WHILE @Counter<@NumRecords

SEE CODE DEPOT  

select order_year, order_month, total, totalsum from @tmpTable

GO

 

The code gets more complicated because it needs to keep track of the current row position and the only way to do it is with a temporary variable that will hold the number of months. This is guaranteed to be unique in every row but has to be calculated repeatedly. The loop is slow but the SELECT and UPDATE statements inside it are the main cause of performance loss.

 

Comparing the different solutions reveals more details:

 

SELECT * FROM myMonthlyCostSum

EXEC myspMonthlyCostSumCur

EXEC myspMonthlyCostSumCur2

EXEC myspMonthlyCostSumTemp

EXEC myspMonthlyCostSumTemp2

EXEC myspMonthlyCostSumCurAlt

 

Example: To calculate the total of all orders by month and the sum of all months until the current one, also included. The total will have the sum of all orders from the months before plus 10%. This is the example that was not properly calculated with a View and there is no alternative to using a cursor for this kind of problem. A recursive solution would be either impossible because recursion is limited to 32 levels or very slow because of the overhead.

 

CREATE PROCEDURE myspMonthlyCostSumCur10

as

--Using Cursor, 10% interest

DECLARE @tmpTable table(order_year int, order_month int, total float, totalsum float)

insert

into @tmpTable

select YEAR(dbo.Orders.OrderDate) , MONTH(dbo.Orders.OrderDate) ,

SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)),0

      FROM  dbo.Orders INNER JOIN

                     dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

      GROUP BY MONTH(dbo.Orders.OrderDate), YEAR(dbo.Orders.OrderDate)

      ORDER BY YEAR(dbo.Orders.OrderDate), MONTH(dbo.Orders.OrderDate)

DECLARE @order_year int, @order_month int, @total money, @totalsum money

SET @totalsum=0

DECLARE cur_med  CURSOR

FOR select order_year, order_month, total from @tmpTable

FOR UPDATE OF totalsum

OPEN cur_med

FETCH NEXT FROM cur_med INTO @order_year, @order_month, @total

WHILE @@FETCH_STATUS = 0     

SEE CODE DEPOT

CLOSE cur_med

DEALLOCATE cur_med

select order_year, order_month, total, totalsum from @tmpTable

GO

 

Buzzword: "fire hose" cursors, are server-side, read-only, forward-only cursors.

 


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter