Cursors and Alternative to the Cursors in Sql server


Introduction:

The cursor is one of the important elements in the sql server. As you know it will do the row by row operation. It will affect the performance of the sql server. Because it will use lot of IO to do the operations. The web application performance not only depends on the application. We have to consider the database also. Because when we want to display the records from the database then it must be effectively do the backend processing. Let us see with an example.

 

Cursors in Sql Server:

The cursors will be used when we want to perform the looping of the table result set. In the sql server we don't have any inbuilt functions to do the looping of the Table results like Array in the any programming language. If you want to iterate the table based on the key then we cannot say it will be in the sequence order.

 

Consider an example; I want to perform the product sales report for every product in the Product Master table. If the product key is an integer then we don't have any problem in such cases. If it is non integer then definitely we cannot iterate.

 

Suppose if you have 10 products in the product table in the order of Product ID is 1,2,3,4,5,6,7,8,9,10. Then we don't have any problem. But two products has deleted then now its order like 1,3,5,6,7,8,9,10. Then there is something needs to perform cursor. Just I take Product table is an example to catch easily.

 

The cursor what it does, it will be stored as a object in the memory then it has the inbuilt row cursor then it will do one by one. Then it will allocate some memory space in the sql server memory, it must be deallocated and closed its references in the memory.

 

USE [Northwind]

GO

 

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

BEGIN

   DROP PROCEDURE Pr_ProductSalesReport

   PRINT '<< Pr_ProductSalesReport procedure dropped >>'

END

GO

 

CREATE PROCEDURE Pr_ProductSalesReport

AS

BEGIN

/*

  Purpose: Calculate the total quantity and sales of the product in 

           product wise.

  Input  : No input specified. It will calculate for all the products

           in Product Master table.

  Output : ProductID,ProductName,Total Quantity and Grand Total of 

           sale.

  Method : The report generated using the Cursor

 

-----------------------------------------------------------------------

*********************** Modification History **************************

-----------------------------------------------------------------------

S.No             Name              Date                   Version

-----------------------------------------------------------------------

1.        Erode Senthilkumar     Sep 01, 2009               1.0

-----------------------------------------------------------------------

*/

    SET NOCOUNT ON

    SET XACT_ABORT ON

 

    DECLARE ProductCursor CURSOR FOR

       SELECT ProductID FROM Products

    DECLARE @ProductID INT

    DECLARE @ProductName VARCHAR(100)

    DECLARE @TotalQty INT

    DECLARE @Total MONEY

   

    DECLARE @ProductSales TABLE

    (

        iSNo INT IDENTITY(1,1)

       ,iProductID INT

       ,vProductName VARCHAR(100)

       ,iTotalQty INT

       ,iGrandTotal MONEY

    )

 

    OPEN ProductCursor

  

    FETCH NEXT FROM ProductCursor INTO @ProductID

   

    WHILE @@FETCH_STATUS = 0

    BEGIN

       SELECT @ProductName = ProductName FROM Products

              WHERE ProductID = @ProductID

       SELECT @TotalQty = SUM(Quantity),@Total = SUM(UnitPrice*Quantity) FROM "Order Details"  

              WHERE ProductID = @ProductID

       INSERT INTO  @ProductSales(iProductID,vProductName,iTotalQty,iGrandTotal)

              VALUES(@ProductID,@ProductName,@TotalQty,@Total)

      

       FETCH NEXT FROM ProductCursor INTO @ProductID

    END

     CLOSE ProductCursor

     DEALLOCATE ProductCursor

  

     SELECT * FROM @ProductSales

END

GO

 

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

BEGIN

   PRINT '<< Pr_ProductSalesReport procedure dropped >>'

END

GO

 

EXEC Pr_ProductSalesReport

GO

 

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

BEGIN

    DROP PROCEDURE Pr_ProductSalesAlternativeReport

    PRINT '<< Pr_ProductSalesAlternativeReport procedure dropped >>'

END

GO

 

CREATE PROCEDURE Pr_ProductSalesAlternativeReport

AS

BEGIN

/*

  Purpose: Calculate the total quantity and sales of the product in

           product wise.

  Input  : No input specified. It will calculate for all the products

           in Product Master table.

  Output : ProductID,ProductName,Total Quantity and Grand Total of

           sale.

  Method : The report generated without using the Cursor

 

-----------------------------------------------------------------------

*********************  Modification History   *************************

-----------------------------------------------------------------------

S.No             Name            Date                   Version

-----------------------------------------------------------------------

1.        Erode Senthilkumar   Sep 01, 2009              1.0

-----------------------------------------------------------------------

*/

    SET NOCOUNT ON

    SET XACT_ABORT ON

 

    DECLARE @ProductID INT

    DECLARE @ProductName VARCHAR(100)

    DECLARE @TotalQty INT

    DECLARE @Total MONEY

    DECLARE @Index INT

    DECLARE @RecordCnt INT

   

    DECLARE @ProdID TABLE(

     iSNo INT IDENTITY(1,1)

    ,iProductID INT

    )

    DECLARE @ProductSales TABLE

    (

        iSNo INT IDENTITY(1,1)

       ,iProductID INT

       ,vProductName VARCHAR(100)

       ,iTotalQty INT

       ,iGrandTotal MONEY

    )

   

    SELECT @Index = 1

 

    INSERT INTO @ProdID(iProductID)

    SELECT ProductID FROM Products ORDER BY ProductID ASC

   

    SELECT @RecordCnt = COUNT(iSNo) FROM @ProdID

   

    WHILE (@Index <= @RecordCnt)

    BEGIN

       SELECT @ProductID = iProductID FROM @ProdID WHERE iSNo = @Index

       SELECT @ProductName = ProductName FROM Products

              WHERE ProductID = @ProductID

       SELECT @TotalQty = SUM(Quantity),@Total = SUM(UnitPrice*Quantity) FROM "Order Details" WHERE ProductID = @ProductID

       INSERT INTO  @ProductSales(iProductID,vProductName,iTotalQty,iGrandTotal)

              VALUES(@ProductID,@ProductName,@TotalQty,@Total)

       SELECT @Index = @Index + 1

    END

     SELECT * FROM @ProductSales

END

GO

 

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

BEGIN

    PRINT '<< Pr_ProductSalesAlternativeReport procedure created >>'

END

GO

 

EXEC Pr_ProductSalesAlternativeReport

GO

The first procedure has implemented using the simple cursor. It will do row-by-row operation using the cursor. It will fetch the record from the cursor result. As we discussed it will take lot of IO reads and it utilizes lot of memory. It will affect the performance of the other query processing in the sql server.

 

The second procedure shows alternative way to implement the cursor. It will give the same result as like stored procedure 1. Here it's like ordinary sql statements using the table variable.

 

As I said when we need the cursor. If you cannot get order of sequence to process the table then we must need some thing like cursor to process row-by-row processing.

Just think we need order of sequence. For that we have created the table variable, what it does it has one identity column and key field which we are going to process the other functionalities.

 

Here consider the Product ID has like this.

1, 3,5,7,8,9,10

 

So definitely I cannot take the product id in the loop to process.

CREATE @ProdTable TABLE (

iSNo INT IDENTITY(1,1),

 ProductID INT

)

 

INSERT INTO @ProdTable(ProductID)

SELECT ProductID FROM Products

 

SELECT * FROM @ProdTable

 

iSNo

ProductID

1

1

2

3

3

5

4

7

5

8

6

9

7

10

 

Now we have the order of sequence in the table. Then we can perform the row by row operations using the looping statements in the sql server.

 

Here the entire product id will be inserted into the table variable. Here we have used the table variable. It will give the more performance. The same result can be achieved using the temp table. As you know the temp table will be stored in the TempDB database. It will work across the database. It will occupy the unnecessary spaces in the sql server memory. It must be deleted once the process gets over.

 

But the table variable is best when we are going to use limited records in the table. Because it will be there in processor memory. Once it cross the level of the data storage then it will move to the tempdb. This life time of the table is only up to that stored procedure.

 

Conclusion:

I hope that this above method will be very useful. I expect your valuable suggestions and corrections. I have attached this script exercise in this article.