Cursor

Cursor

• Allows row-by-row processing. Control will be with user rather than SQL server
• Different options can be applied while creating a cursor

o Local/global

o Static/dynamic/fast_forward/ketyset driven

o Forward_only/scroll

• Generally it should be avoided as it is affecting a performance a big time
• If another SP is being called within cursor, it will fail. So while loop should be used instead of cursor
• A general syntax

DECLARE cur CURSOR

FOR SELECT a,b FROM TableName

OPEN cur

FETCH NEXT FROM Cur INTO @a, @b

WHILE @@FETCH_STATUS = 0

BEGIN

-- Your logic

FETCH NEXT FROM Cur INTO @a, @b

END

CLOSE cur

DEALLOCATE cur

• In some cases, Cursor will be faster than normal join e.g. 
• when there is some N level of parent-child relationship than rather than joining the same table multiple times, we can use cursor and achieve the results fast
• When you have to insert millions of record in one table then probably you can start cursor and insert a bunch of records (let's say 25K records) in chunks which will be faster than a single INSERT statement
• Even if we have to use cursor, it is recommended that we use WHILE LOOP rather than using cursor

Alteration of CURSOR

As we all know that the cursor is a bad performer. We always try to avoid cursor.

So the question is how we avoided the cursor? This article give us the demonstration, how to avoid the cursor. 

In this example i am trying to make a stored procedure for Calculate the total quantity and sales of the product in product wise. It will calculate for all the products  in Product Master table.

  

First example is using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

    SET NOCOUNT ON

    DECLARE my_cursor CURSOR

            FOR SELECT pID

                FROM   m_Products

           

    DECLARE @pID    INT

    DECLARE @pName  VARCHAR(100)

    DECLARE @topQty INT

    DECLARE @tot    MONEY

  

    DECLARE @prod_Sale TABLE

                  (

                        srlId     INT IDENTITY(1,1),

                      iProdID   INT,

                      vProdName VARCHAR(100),

                      iTotQty   INT,

                      iGrandTot MONEY

                  )

    OPEN my_cursor

    FETCH NEXT FROM my_cursor INTO @pID

  

    WHILE @@FETCH_STATUS = 0

            BEGIN

               SELECT @pName = prod_name

               FROM   m_Products

               WHERE  pID = @pID

              

               SELECT @topQty = SUM(qty),

                      @tot    = SUM(unit_Price*qty)

               FROM   tran_Orderdetail  

               WHERE  pID = @pID

              

               INSERT INTO  @prod_Sale

                           (iProdID,vProdName,iTotQty,iGrandTot)

               VALUES(@pID,@pName,@topQty,@tot)

           

               FETCH NEXT FROM my_cursor INTO @pID

            END

           

     CLOSE my_cursor

     DEALLOCATE my_cursor

     SELECT * FROM @prod_Sale

END

GO

EXEC Pr_ProdSalesRpt

Now I am trying to convert it without cursor.

It is quite simple and just needed some programmatic technique to control loop… etc.

Example to convert the stored procedure WITHOUT using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

      SET NOCOUNT ON

    SET XACT_ABORT ON

    DECLARE @pID       INT

    DECLARE @pName     VARCHAR(100)

    DECLARE @topQty    INT

    DECLARE @tot       MONEY

    DECLARE @Index     INT

    DECLARE @RecordCnt INT

  

    DECLARE @ProdID TABLE

            (

              srlId   INT IDENTITY(1,1),

              iProdID INT

            )

    DECLARE @prod_Sale TABLE

                  (

                        srlId      INT IDENTITY(1,1),

                      iProdID    INT,

                      vProdName  VARCHAR(100),

                      iTotQty    INT,

                      iGrandTot  MONEY

                  )

  

    SELECT @Index = 1

    INSERT INTO @ProdID

               (iProdID)

    SELECT   pID

    FROM     m_Products

    ORDER BY pID ASC

  

    SELECT @RecordCnt = COUNT(srlId)

    FROM   @ProdID

  

    WHILE (@Index <= @RecordCnt)

            BEGIN

                     SELECT @pID = iProdID

                     FROM   @ProdID

                     WHERE  srlId = @Index

                    

                     SELECT @pName = prod_name

                     FROM   m_Products

                     WHERE  pID = @pID

                    

                     SELECT @topQty = SUM(Quantity),

                            @tot    = SUM(UnitPrice*Quantity)

                     FROM   tran_Orderdetail

                     WHERE  pID = @pID

                    

                     INSERT INTO  @prod_Sale

                       (iProdID,vProdName,iTotQty,iGrandTot)

                     VALUES(@pID, @pName, @topQty, @tot)

                    

                     SELECT @Index = @Index + 1

            END

           

     SELECT * FROM @prod_Sale

END

GO

I think the construction of without CURASOR is quite easy and it definitely improve the performance.

Hope you like it.

CURSOR Type

Sometimes it is necessary to use the cursor, as we know that the cursor is a big performance killer and we always avoid it. But we can save some performance degradation if we know the type of cursor very well.

This article illustrates the cursor type used in SQL Server. Proper use of cursor type is necessary for increase the performance and for operational use.

The cursor type is declared when we declare the cursor. There are 5 types of cursor mentioned bellow.

STATIC

Static cursor is a fixed snapshot of a set of rows. This fixed snapshot is stored is a temporary database (tempdb). As the cursor is used private snapshot any changes to the set of rows external will not visible in the cursor while browsing through it.

Syntax:

DECLARE <cursor_name> CURSOR STATIC

        FOR   SELECT *

              FROM   <Table_name>

              WHERE  <Conditions>

KEYSET

In KEYSET the key value of the rows are saved in tempdb. For instance let’s says the cursor has faced the following bellow data. So only the “supplierid” will be stored in tha databse. Any new instance happening is not reflected in the cursor. But any updated in the keyset values are reflected in the cursor. Because the cursor is identified by key values we can also absolutely fatches them using “FETCH ABSOLUTE 12 FROM mycursor”

DYNAMIC

In dynamic cursor you can see any kind of changes happening. That means inserting new records or changing the existing records and even record deletions. That’s why the dynamic cursor are slow and have least performance.

FORWARD_ONLY

As the name suggest they only move forward and only a one time featch is done. In every fetch the cursor is evaluated. That means any changes to the data are known, until we have specified “STATIC” or “KEYSET”

FAST_FORWARD

This type of cursor is forward only and read-only in every fetch they are not re-evaluted again. This make them a good choice to increase performance.

GLOBAL and LOCAL cursors

GLOBAL are global for a connection. By default cursor are global. That means you can declare a cursor in one stored procedure and access it outside also.

Locate cursor are accessable only inside the object(SP or Trigger or Function).

We can declare a cursor as LOCAL or GLOBAL in the DECLARE cursor sysntax.

Syntax:

DECLARE <cursor_name> CURSOR [ LOCAL | GLOBAL ]

[ STATIC | KEYSET | DYNAMIC | FORWARD_ONLY  | FAST_FORWARD ]

        FOR   SELECT *

              FROM   <Table_name>

              WHERE  <Conditions>

Hope you like it.

Don’t Use CURSOR

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications and take more I/O resources. If you need to perform row-by-row operations, try to find another method to perform the task.

Some the recommended alternative of the cursor are mentioned bellow:

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries

If there is no alternative, and you need to use a cursor remember some tips related to it. It can save you little more.

1..  One way to do this is to move the records that need to be processed into a temp table first, then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subsets of records to be inserted into the temp table are substantially less than those in the original table.

2.. If the number of rows you need to return from a query is small, and you need to perform row-by-row operations on them, don't use a server-side cursor. Instead, consider returning the entire rowset to the client and have the client perform the necessary action on each row, then return any updated rows to the server.

3.. If you have no choice but to use a server-side cursor in your application, try to use aFORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of theFORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

3.. Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues.

4.. If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors may use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.

5.. Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid theSCROLL_LOCKS cursor option, which reduces concurrency.

6.. When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.

7.. If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.

8.. If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment.

9.. When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit.

10.. If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.

11.. If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance.

12.. In SQL Server, there are two options to define the scope of a cursor. LOCAL and GLOBAL keywords in the DECLARE CURSOR statement are used to specify the scope of a cursor. A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. GLOBAL cursors must be explicitly deallocated or they will be available until the connection is closed. For optimum performance, you should always explicitly deallocate a cursor when you are done using it. LOCAL cursors are implicitly deallocated when the stored procedure, the trigger, or the batch in which they were created terminates. We can use LOCAL cursors for more security and better scope of the cursor in our application, which also helps to reduce resources on the server, boosting performance. Contributed by Nataraj Prakash. 

Cursor Function

This article is related to cursor function. Here in this article I am trying to mention each function in simple way that you can use it when you are using the cursor.

Cursor function pays a very important rows when you playing with cursor. The cursor function is non deterministic, means it not returns the same result set if you call them with same input value.

 There are three scalar function of the cursor.

1.    @@CURSOR_ROWS

2.    CURSOR_STATUS

3.    @@FETCH_STATUS

@@CURSOR_ROWS

We can get, how many rows will be return by the cursor currently the last cursor open by the connection. @@CURSOR_ROWS is negative if the last cursor was opened asynchronously. Keyset-driver or static cursors are opened asynchronously if the value for sp_configurecursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

Return value	Description
-m	The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1	The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0	No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n	The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.

SELECT @@CURSOR_ROWS;

DECLARE Name_Cursor CURSOR FOR

              SELECT itemdescr ,@@CURSOR_ROWS FROM itemmaster whereitemcode='0000002';

OPEN Name_Cursor;

FETCH NEXT FROM Name_Cursor;

SELECT @@CURSOR_ROWS;

CLOSE Name_Cursor;

DEALLOCATE Name_Cursor;

GO   

@@FETCH_STATUS

It returns the status of last cursor FETCH

Return value	Description
0	The FETCH statement was successful.
-1	The FETCH statement failed or the row was beyond the result set.
-2	The row fetched is missing.

DECLARE my_cur CURSOR FOR

                      SELECT id, itemdescr

                      FROM   itemmaster;

OPEN my_cur;

FETCH NEXT FROM my_cur;

WHILE @@FETCH_STATUS = 0

   BEGIN

      <.....>

      FETCH NEXT FROM my_cur;

   END;

  

CLOSE my_cur;

DEALLOCATE my_cur;

GO

CURSOR_STATUS

Determines that the cursor is still open or NOT. When you are using multiple cursor, it is very useful.

Return value	Cursor name	Cursor variable
1	The result set of the cursor has at least one row. For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.	The cursor allocated to this variable is open. For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.
0	The result set of the cursor is empty.*	The cursor allocated to this variable is open, but the result set is definitely empty.*
-1	The cursor is closed.	The cursor allocated to this variable is closed.
-2	Not applicable.	Can be: No cursor was assigned to this OUTPUT variable by the previously called procedure. A cursor was assigned to this OUTPUT variable by the previously called procedure, but it was in a closed state upon completion of the procedure. Therefore, the cursor is deallocated and not returned to the calling procedure. There is no cursor assigned to a declared cursor variable.
-3	A cursor with the specified name does not exist.	A cursor variable with the specified name does not exist, or if one exists it has not yet had a cursor allocated to it.

IF (SELECT CONVERT(DECIMAL,CURSOR_STATUS('global','my_CUR')))>0

        BEGIN

          CLOSE my_CUR  

              DEALLOCATE my_CUR

        END

Hope the article is quite informative and thanking you to provide your valuable time on it.

Is API Cursor is there

Once again, I find something that can hamper the performance of my SQL query, please have a look, it may be quite informative.

Some time the SQL statement take so long time (in distributed  query in remote server environment) to execute and it takes a long CPU time and sometimes it's so slow that u can go outside take some snakes and till it is running.

In this type of condition we always think about missing index or table scan is the main culprit. By using profiler for any others executions tolls you find that alls are ok the main one is API server cursor, so I try to illustrate this in my blog.

SQL Server OLEDB provider use 2 types of methods for fetching data form distributed server.

1.    A default result set will fetch the results of the distributed query from a remote server in a single batch. 

2.    The other method utilizes a cursor, referred to as an API Server Cursor, to fetch the results of the distributed query from the remote server.

When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to:

1. Open a connection.
2. Set attributes or properties defining the characteristics of the cursor the API automatically maps over each result set.
3. Execute one or more Transact-SQL statements.
4. Use API functions or methods to fetch the rows in the result sets.

In SQL profiler when we find the "Stored procedure-RPC compiled" that means that the procedure that used by API Server cursors.

Example of distributed query

SELECT  *

FROM    RemoteServer.RemoteDatabase.dbo.RemoteTable0 as t0

              INNER JOIN RemoteServer.RemoteDatabase.dbo.RemoteTable1 as t1

              ON t0.Account = t1.Account

WHERE   t0.Account = 'XYZ'

In SQL server profiler trace

The TextData column for the RPC:Completed events expose the execution of the system stored procedure, sp_cursorfetch.

For each execution of this system stored procedure a round-trip between the two servers is made.  The previous statement and the results of the SQL Server Profiler trace should clearly illustrate why using an API Server Cursor has the potential to quickly become a major performance problem.  Elaborating on this, the sp_cursorfetch system stored procedure is executed as a series of batches until all of the data has been returned from the remote server.  The last parameter passed into the sp_cursorfetch system stored procedure is the number of records that are processed for each execution. 

In this case, 100 records are processed for each execution of the sp_cursorfetch system stored procedure – Therefore, my example processes about 6000 records in total, for a series of 60 executions with each execution making a round-trip between the servers.

For better understanding http://msdn.microsoft.com/en-gb/library/aa172588(SQL.80).aspx