Lesson 3: Designing Efficient Cursors

Estimated lesson time: 45 minutes

A quick Internet search will show you several articles and posts claiming that cursors are a bad thing. Developers who are unfamiliar with SQL Server tend to use many cursors because they are easy to write and follow. Because cursors perform operations one record at a time, looping through a cursor with a large set of records can take a long time to process. Many experienced developers avoid cursors because they can often find a set-based query that accomplishes the same result more quickly. While it is true that the logic in most cursors can be replaced with set-based logic, there are some cases where cursors are warranted and possibly faster than other alternatives. This section will highlight cases where cursors are a good choice and discuss the options that you can use when designing the logic.

Using Scrollable Cursors

Some application specifications might require you to scroll forward and backward among a set of results. For example, it might be necessary to retrieve a set of results, and then, based on the user input, scroll either forward or backward through the result set. This is a situation in which cursors might be the most efficient choice. By being able to scroll forward and backward through the results, you can eliminate the need to perform multiple queries.

The following fetch options can be used when working with a scrollable cursor:

Fetch First Retrieves the first row in the cursor.
Fetch Last Retrieves the last row in the cursor.
Fetch Next Retrieves the next row in the cursor.
Fetch Prior Retrieves the row before the last one fetched, unless you have just opened the cursor, or you are positioned at the first record.
Fetch Absolute n Retrieves the position specified with the n parameter, which can be set as a positive integer, negative integer, or a zero. If n is set with a positive value, then it will move that number of places from the first row. If n is set with a negative value, then it will move that number of places from the last row. If n is set with a zero, then no rows are fetched.
Fetch Relative n Retrieves the position specified, relative to the last row that was fetched. If n is set with a positive integer, then it will move that number of places after the last row is fetched. If n is set with a negative value, then it will move that number of places before the last row is fetched. If n is set with a zero, the same row is fetched again.

The scrollable cursor can be specified by using the SCROLL option instead of the FORWARD_ONLY option. For example, the following Transact-SQL can be used to create a scrollable cursor named crsrScroll:

 DECLARE crsrScroll   CURSOR  SCROLL FOR   SELECT [Name], ProductNumber,        StandardCost, ListPrice   FROM Production.Product

SQL Server 2005 offers a built-in function named @@CURSOR_ROWS that can be used to return the number of rows in a cursor. For example, the following Transact-SQL statement can be used to return the product number along with the number of rows in the cursor:

 SELECT ProductNumber, @@CURSOR_ROWS FROM Production.Product

Processing on a Row-by-Row Basis

When there is a need to perform processing, such as the execution of a stored procedure on a row-by-row basis, it is possible for a cursor to perform more quickly than a set-based alternative. For example, the following stored procedure can be used to loop through all the products in the AdventureWorks database and, based on the value of an input variable, execute the stored procedure sp_SomeStoredProcedure. The cursor will then perform an INSERT into the Production.ProductCostHistory table if the stored procedure was executed successfully.

 DECLARE crsrRowByRow   CURSOR  FAST_FORWARD FOR   SELECT ProductNumber, ListPrice,    StandardCost   FROM Production.Product OPEN crsrRowByRow FETCH NEXT FROM crsrProducts   INTO @ProdNum, @Listprice,          @StdCost WHILE @@FETCH_STATUS = 0 BEGIN    IF (@inVar = "Some Value")    BEGIN          EXEC @retcode = sp_SomeStoredProcedure                 @product_num = @ProdNum,                 @list_price = @Listprice,                      @sp_var = @inVar    END    IF @retcode = <> 0    BEGIN          INSERT INTO Production.ProductCostHistory                VALUES (@StartDate, @EndDate, @StdCost, GetDate())    END    FETCH NEXT FROM crsrProducts      INTO @ProdNum, @LstPrice,                @StdCost END CLOSE crsrRowByRow DEALLOCATE crsrRowByRow

It is possible that the cursor named crsrRowByRow would execute more quickly than a set-based alternative. This would depend on various factors, such as the number of records to be processed and the efficiency of the code in the stored procedure. The only way to know for sure would be to compare execution times for both methods.

Using Dynamic SQL

You can use dynamic SQL to build your cursors. This is done in the same way you would issue any SQL statement using dynamic SQL. You will need to include the DECLARE statement inside the dynamic SQL string. For example, the following Transact-SQL can be used to create a cursor named crsrProducts using dynamic SQL:

 DECLARE @Color nvarchar(15) SET @Color = 'Black' DECLARE @sql nvarchar(255) SELECT @sql = 'DECLARE crsrProducts CURSOR FAST_FORWARD FOR ' +   'SELECT ProductNumber, ListPrice,StandardCost '+   'FROM Production.Product ' +   'WHERE Color = ''' + @Color + ''';' +   'OPEN crsrProducts '   'WHILE (@@FETCH_STATUS = 0) ' +   'BEGIN ' +   'FETCH NEXT FROM crsrProducts ' +   'END; '   'CLOSE crsrProducts ' +   'DEALLOCATE crsrProducts' EXEC sp_executesql @sql

Important

Security alert

The use of dynamic SQL can make your application vulnerable to SQL injection attacks. This enables malicious parties to gain control of your SQL Server by sending certain commands through the input parameters used to build the dynamic SQL.

When using dynamic SQL to create your cursors, you must take care to still close and deallocate these cursors. This also applies if you add any type of error handling. You want to ensure that the cursor will be removed from memory, even if an error occurs.

Selecting a Cursor Type

As described in Lesson 2, you can improve cursor performance by using a FAST_FOWARD or FORWARD_ONLY cursor type. In addition to these cursor types, there are others that you might need to consider. This section highlights the things you need to consider when selecting a cursor type.

There are four types to consider when declaring your cursor. They are as follows:

Forward Only This is the fastest performing cursor; it moves in one direction only. It does not support scrolling. It is related to the FAST_FORWARD type, which is just a FORWARD_ONLY, READ_ONLY cursor.
Static Compared to dynamic and keyset-driven cursors, this one is the fastest. It uses a snapshot of the data taken when it was opened and detects no changes since then.
Keyset-driven This option uses a set of keys, known as a keyset, to uniquely identify the rows in the result set. It can be used to detect most changes to the result set, so it is slightly more efficient than the dynamic cursor, but less efficient than a static one.
Dynamic A dynamic cursor is the opposite of a static cursor, but it also consumes the most resources. This cursor can be used to detect changes made to the result set when scrolling through the cursor. When dealing with a large result set, it can be faster to open than a static or keyset-driven cursor.

It is possible for a cursor to be forward only and static, keyset, or dynamic. Alternatively, it can be marked with the SCROLL option and be static, keyset, or dynamic. For example, you could have a cursor such as the following:

 DECLARE crsrProducts   CURSOR  SCROLL STATIC FOR   SELECT ProductNumber, ListPrice,    StandardCost   FROM Production.Product

If the SCROLL option is used, then all of the fetching capabilities featured in the previous section are available. Alternatively, if the cursor is marked as FORWARD_ONLY SCROLL, then it will only be able to move from start to finish.

Evaluating Cursor Efficiency

SQL Server 2005 is, first and foremost, a relational database, not a programming environment. Transact-SQL is a set-based language that was never designed to be an optimized object-oriented programming language. Cursors have their place in the SQL Server arsenal, but you need to be careful not to overuse them.

Utilizing Server Memory

A common mistake for new or inexperienced developers is to immediately create a cursor when another alternative would be more appropriate. Creating a cursor might be the easiest thing to do, but if you choose to use cursors all the time, you could be overtaxing the server's memory. Though you can simply choose to add more memory to the server, this is not always the best solution.

When faced with a situation where row-by-row processing needs to occur, do not immediately assume that a cursor is the best alternative. First, evaluate the other alternatives, such as a single SELECT statement or a WHILE loop. If you determine that a cursor is absolutely necessary, you need to design it so that it utilizes memory efficiently.

When designing your cursors, you should consider the following:

The SELECT statement should return the least amount of data possible. Limit the number of columns returned and query from a temporary table if possible.
Use a FAST_FORWARD or FORWARD_ONLY cursor if no updates will be performed.
If you are using static or keyset-driven cursors, which build a temporary table, move the tempdb to a set of separate high-performance disk spindles in the form of a disk array.
Break out of the cursor as soon as possible. If you accomplish your goal before the last record in the result set is reached, exit the cursor WHILE loop at that time to prevent unnecessary looping.
If the cursor SELECT statement needs to perform JOINS, consider using static or keyset-driven cursors, as opposed to dynamic ones.
Close and deallocate cursors as soon as possible.

Minimizing Blocking

The sensitivity of a cursor indicates that a change made to the cursors data is immediately visible. When declaring a cursor, you can use either the SQL-92 or Transact-SQL extended syntax.

The SQL-92 syntax uses the INSENSITIVE keyword to indicate sensitivity. By using this option, you can achieve slightly faster performance because SQL Server does not need to keep checking for updates to the data. You can also include the FOR READ_ONLY option to ensure that the cursor is not available for updates. For example, the following statement uses the SQL-92 syntax to create a cursor that is not sensitive to changes:

 DECLARE crsrProducts  INSENSITIVE   CURSOR FOR   SELECT ProductNumber, ListPrice,    StandardCost   FROM Production.Product FOR READ_ONLY

Cursor concurrency is used to specify how locks are handled for the underlying data tables of a cursor. The Transact-SQL extended syntax supports the following three concurrency options:

READ_ONLY This option specifies that no updates can be made to the underlying data for that cursor. It is considered the most efficient option because it ensures that no locks will be held on the rows that make up the result set.
OPTIMISTIC This option indicates that there is only a slight chance that an update will take place while the cursor is being used. Instead of issuing a lock on the underlying table, this option indicates that if a user changes the data in the cursor, a check will be made against the data in the database first. If the data has changed since it was originally retrieved, then an error will be issued.
SCROLL_LOCKS This is the most thorough option and the most expensive in terms of resource usage. Locks will be issued based on locking hints in the cursor SELECT statement. This option should be used when you expect data to change before the cursor processing ends.

The Transact-SQL extended syntax uses the SCROLL_LOCKS option to specify that positioned updates can occur within the cursor. For example, the following statement uses the Transact-SQL extended syntax to create a cursor that is updateable:

 DECLARE crsrProducts   CURSOR DYNAMIC SCROLL_LOCKS FOR   SELECT ProductNumber, ListPrice,    StandardCost   FROM Production.Product FOR UPDATE OF StandardCost

These updates are guaranteed to succeed, which means that SQL Server will need to hold a lock on the table to ensure this. For this reason, you want to minimize the use of this option. This option cannot be used with the FAST_FORWARD cursor type. If you know that no updates need to occur, the cursor should be marked with the READ_ONLY concurrency option.

Minimizing or Eliminating Cursors

If your SQL Server responds slowly and applications start to experience performance problems, the best thing to do is to evaluate the Server's use of indexes. The next thing to do is to look at the Transact-SQL code running on your server. It is not uncommon to see cursors over-used in stored procedures or user-defined functions.

If you see that cursors are being used too much, try to identify which are causing the most performance problems. This can be done by running a trace using SQL Server Profiler while the application is executing. SQL Server Profiler is accessible from the Tools menu in Microsoft SQL Server Management Studio. It enables you to select the events to be monitored by the trace. The trace can be configured to specifically monitor whether a cursor was opened, closed, or executed, as shown in Figure 3-3.

image from book
Figure 3-3: Configuring the SQL Server Profiler trace to monitor specific events

Once you have identified the longest executing cursors, you can start to analyze the Transact-SQL within those cursors. You might be able to speed up the cursor by making a few small changes, such as making it READ_ONLY and FORWARD_ONLY. At this point, you should consider whether there is an alternative for the cursor. If an alternative is found, make sure you execute both methods to determine which is the fastest.

Lab: Designing Cursors

In this lab, you will create a scrollable cursor and practice evaluating cursor efficiency. The first exercise will deal with creating a cursor that moves back and forth as it is looped. In the second exercise, you will use SQL Server Profiler to evaluate the cursor created in exercise 1.

Exercise 1: Create a Scrollable Cursor

In this exercise, you will create a scrollable cursor using the scroll option and one of the fetch methods.

Open Microsoft SQL Server Management Studio.
Connect to the instance of SQL Server 2005 that contains the AdventureWorks database.
Select New Query.

Add the following code to the query window:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE spGetPriorProduct   @InProdNum nvarchar(25) AS BEGIN   -- Declare variables that we will store fetch   -- results in   DECLARE @ProdName nvarchar(50)   DECLARE @ProdNum nvarchar(25)   DECLARE @StdCost money   DECLARE @ListPrice money   SET NOCOUNT ON;   -- Allocate and define the cursor   DECLARE crsrProducts          SCROLL CURSOR   FOR          SELECT [Name], ProductNumber,                StandardCost, ListPrice          FROM Production.Product          ORDER BY ProductNumber   -- Open the cursor and fetch the results   -- into the local variables    OPEN crsrProducts    FETCH NEXT FROM crsrProducts      INTO @ProdName, @ProdNum,            @StdCost, @ListPrice   -- Loop through the cursor while the fetch   -- is still successful   WHILE @@FETCH_STATUS = 0   BEGIN       IF @ProdNum = @InProdNum        BEGIN                --Get the previous record and                --return the data for that record                FETCH PRIOR FROM crsrProducts                      INTO @ProdName, @ProdNum,                   @StdCost, @ListPrice                -- Print out the results to the messages window                PRINT 'Product: ' + @ProdName + ' (' + @ProdNum + ') '                      + 'Cost: ' + Cast(@StdCost as varchar(8))                      + ' Price: ' + Cast(@ListPrice as varchar(8))                --Exit the loop                BREAK         END          -- Get the next set of results          FETCH NEXT FROM crsrProducts               INTO @ProdName, @ProdNum,             @StdCost, @ListPrice    END    CLOSE crsrProducts    DEALLOCATE crsrProducts END

Select the AdventureWorks database from the Available Databases drop-down list, and then click Execute.
Add the following code to the bottom of the query window, highlight it, and click Execute:
```
 exec spGetPriorProduct 'BB-7421' 
```
You should see the following result displayed in the messages box. This is the record that exists prior to the product with a product number of BB-7421.
```
 Product: Bearing Ball (BA-8327) Cost: 0.00 Price: 0.00 
```

The completed lab is available in the \Labs\Chapter 03 folder on the companion CD.

Important

Lab requirements

You will need to have SQL Server installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 2: Examine a Cursor

In this exercise, you will use SQL Server Profiler to examine the execution of a cursor.

Open SQL Server Profiler by clicking Start, All Programs, Microsoft SQL Server 2005, Performance Tools, SQL Server Profiler.
Click File, New Trace.
Enter the connection information for the server that contains the AdventureWorks database, and then click Connect.
From the Trace Properties dialog box, enter a name for the trace, and select the Events Selection tab.
Select the Show all events check box, and scroll to the Stored Procedures event class and expand that node. Select the check boxes for all stored procedure events.
Click Run to begin the trace. Do not close SQL Server Profiler or close the Trace Properties dialog box.
Open SQL Server Management Studio.
Connect to the instance of SQL Server 2005 that contains the AdventureWorks database.
Select New Query.
Add the following code to the query window:
```
 exec spGetPriorProduct 'BB-7421' 
```
Select the AdventureWorks database from the Available Databases drop-down list, and then click Execute.
Return to SQL Server Profiler, and stop the trace by clicking the Stop button on the toolbar.
Scroll through the trace results and notice how many events were captured for the execution of the stored procedure. (See Figure 3-4.)

image from book
Figure 3-4: Trace results for a trace performed while executing the spGetPriorProduct stored procedure.