Archive

Posts Tagged ‘Cursor’

Avoid CURSORS? why not use them optimally…

February 7, 2011 3 comments

CURSORS or WHILE loops with temp-tables & counter, what do you prefer, personally and perofrmance wise?

This has been discussed in lots of forums, threads, posts and blogs previously. Many people and experts claim to use the either one and most of them are inclined to WHILE loops, and suggest to avoid CURSORS without any proof and logic.
Links: http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx
http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx
… and many more.

To make this more clear I tested this scenario myself and also posted this case in MSDN TSQL forum.

use [tempdb]
GO

create table T1 (sn int identity(1,1) primary key, data varchar(1000))
GO

insert into T1 (data)
values (replicate('a',1000))
GO 10000

select * from T1

create table T2 (sn int primary key, data varchar(1000))
create table T3 (sn int primary key, data varchar(1000))

-- Test the CURSOR, pull record from T1 and insert into T2
set nocount on

declare @stDate datetime
set @stDate = getdate()
declare @sn int , @data varchar(1000)

declare cr cursor FORWARD_ONLY FAST_FORWARD READ_ONLY
for select sn, data from T1

open cr
fetch next from cr into @sn, @data
while @@fetch_status=0
begin
	insert into T2
	select @sn, @data
	fetch next from cr into @sn, @data
end
close cr
deallocate cr

select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me 966, 413, 310, 306 ms
GO

-- Test the WHILE loop with counter (NO CURSOR), pull record from T1 and insert into T3
set nocount on

declare @stDate datetime
set @stDate = getdate()
declare @ctr int
set @ctr=0

while @ctr<=10000
begin
	insert into T3
	select sn, data
	from T1
	where sn = @ctr

	set @ctr = @ctr + 1
end

select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me: 1070, 450, 503, 423 ms
GO

--Final Cleanup
drop table T2
drop table T3
drop table T1

I ran the above code for CURSOR & WHILE loop 4 times and it gave me less execution time for CURSOR, everytime.

While using CURSORS the main thing most people miss out are the options that are available to optimize CURSOR use.
According to MS BOL they are:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

To know more check this link: http://msdn.microsoft.com/en-us/library/ms180169.aspx

This prove that CURSORs are more performant than WHILE loops. I’m open for your comments, thanks.

MSDN links:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e67105a6-0f4a-4a12-85b9-e7e9855279e7/
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2978d387-fcd0-45bb-bf69-80139b6dac53
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ba15132e-c26d-4472-92e9-845ce2cc244d/

SQL Server CURSOR and it’s Life Cycle

October 24, 2010 1 comment

A CURSOR in SQL is a database object that contains a set of records that you can traverse one-by-one, rather than the SET as a whole.

SQL is a set-based language and produces a complete result set, and the SQL queries works on this complete set only, and not on individual rows. But there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

CURSOR in SQL language gives you the flexibility to traverse records like the way you do in other programming languages with iterators and for-loop.
 

–> A simple Cursor life cycle with minimum definition:

USE [AdventureWorks]
GO

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Before Declare'

DECLARE myCursor CURSOR
FOR SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID BETWEEN 100 and 102

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'After Declare'

DECLARE @ContactID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

OPEN  myCursor

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'Open Cusrsor'

FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
WHILE @@fetch_status=0
BEGIN

	-- SQL Statements with logic inside
	SELECT @ContactID, @FirstName, @LastName

	FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
END

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'While loop exited, all rows iterated'

CLOSE myCursor

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor is Closed'

DEALLOCATE myCursor

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor Deallocated'

 

Do check & Like my FB Page.