Sunday, August 4, 2013

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,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-FORWARDREAD-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.