Set Based Query Vs Cursor
What is cursor?
Cursor is a database object used to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In other words, we can say its like record set in the visual basic and ASP.
By using cursor, we can perform detailed data manipulation on a row-by-row basis. It is very flexibility and easy to use and understand.
Drawbacks of Cursor:
One of the major downside of cursor is performance. Because of row-by-row fetching of records, it will take more time. For accessing the remote database for the large project, the fetching time will be more and therefore the performance is very low.
To overcome this downside, we go for set based query.
what is Set Based Query?
Implementation of cursor is procedure based approach. Querying the database to obtain a result set and using a Cursor to navigate the result set to do further processing row by row is called as procedural approach.
But set based query is Set based approach. By using this approach you define what set of data you want or want to operate on and the atomic operation to apply to each element of the set.
Example for Procedure Based Approach using the Cursor:
--A variable to hold the currently selected value from the cursor
DECLARE @chvCurrentValue varchar(300);
-- Declare a cursor
DECLARE Customer_cur CURSOR FOR
SELECT Name FROM dbo.Customer
-- Open a cursor
Open Customer_cur
FETCH NEXT FROM Customer_cur INTO @chvCurrentValue
WHILE @@FETCH_STATUS = 0
BEGIN
--Process the current value
PRINT @CurrentValue
-- Fetch Next Record
FETCH NEXT FROM Customer_cur INTO @chvCurrentValue
END
CLOSE Customer_cur
DEALLOCATE Customer_cur
Example for SET Based Approach using the Table Variable:
--Declare the Table variable
DECLARE @Customer_tbl TABLE
(
CustomerID INT IDENTITY(1,1), --Auto incrementing Identity column
CustomerName VARCHAR(300) --The string value
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @intIndex INT
--Decalre a variable to remember the number of rows in the table
DECLARE @intCount INT
--A variable to hold the currently selected value from the table
DECLARE @chvCurrentValue varchar(300);
--Populate the TABLE variable using some logic
INSERT INTO @Customer_tbl SELECT Name FROM dbo.Customer
--Determine the number of rows in the Table
SET @intCount= @@ROWCOUNT
--Initialize the loop variable
SET @intIndex = 1
--Loop through until all row processing is done
WHILE @intIndex <= @intCount
BEGIN
--Load current value from the Table
SELECT @chvCurrentValue = CustomerName FROM @Customer_tbl WHERE
CustomerID = @intIndex
--Process the current value
PRINT @CurrentValue
--Increment loop counter
SET @intIndex = @intIndex + 1;
END
Above example will improve the performance over the cursor by pulling set of data from the database and process it very quickly using the Table variable. Because table variables are mostly ‘in memory’ variables, so, I/O in table variables is bound to be fast. No need to release the resource explicitly if you are using Table variable instead of temporary table.
|
| Author: Alwyn 22 Oct 2009 | Member Level: Gold Points : 1 |
For some reason, table variable has some disadvantages;
1) It has a limited scope and are not part of persistent database.
2) Transaction rollback does not affect them.
|
| Author: Melchior 23 Oct 2009 | Member Level: Gold Points : 2 |
Hi,
You are correct.Table variable and temporary table have some advantages and also have some drawbacks.
Use of Table variable and temporary table depends on the requirements.
If you are going to call nested procedure within the While Loop then we must use temporary table. Most of the case,Table Variable will be very helpful to improve the performance.
No need to release resource explicitly because of Limited scope of Table Variable.Limited scope is not a disadvantage. This is the design of Table variable.If we need to use within the limited scope, we can go for Table variable otherwise we must use temporary table.
|