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.