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.

Posted by: MR. JOYDEEP DAS

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.

Posted by: MR. JOYDEEP DAS

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 a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_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 the SCROLL_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. 

Posted by: Mr. Joydeep Das