Hey folks till now we have discussed User defined functions in sql, Kill Processes in sql server 2008, View in sql , default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

Today we will discuss regarding Performance improvement of cursor SQL Server

Sample example of Cursor without any optimization

 

Lets start with simple example of cursor and try to run that example & measure execution time.

	
USE tempdb;
GO

IF OBJECT_ID(N'dbo.technothirsty',N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.technothirsty;
END

SELECT s1.name INTO dbo.technothirsty FROM sys.all_columns AS s1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n2
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n3
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n4

CREATE CLUSTERED INDEX x ON dbo.technothirsty(name);
GO

DECLARE @name SYSNAME, @i INT=0;

DECLARE c CURSOR
FOR SELECT name FROM dbo.technothirsty;

OPEN c;

FETCH c INTO @name;

WHILE @@FETCH_STATUS=0
BEGIN
SET @i+=1;
FETCH c INTO @name;
END

CLOSE c;
DEALLOCATE c

 

We have run above query in sql server and measured execution time, it took 47 seconds to execute which is as below:

 

 

Apply FAST_FORWARD to optimize query

 

	
USE tempdb;
GO

IF OBJECT_ID(N'dbo.technothirsty',N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.technothirsty;
END

SELECT s1.name INTO dbo.technothirsty FROM sys.all_columns AS s1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n2
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n3
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n4

CREATE CLUSTERED INDEX x ON dbo.technothirsty(name);
GO

DECLARE @name SYSNAME, @i INT=0;

DECLARE c CURSOR FAST_FORWARD
FOR SELECT name FROM dbo.technothirsty;

OPEN c;

FETCH c INTO @name;

WHILE @@FETCH_STATUS=0
BEGIN
SET @i+=1;
FETCH c INTO @name;
END

CLOSE c;
DEALLOCATE c

 

We have added FAST_FORWARD at the end of declaration of Cursor, now we got execution time 26 seconds as below:

 

FAST_FORWARD will give order to go forward only while cursor traverse through loop.

 

Apply LOCAL FAST_FORWARD to further optimization

 

	
USE tempdb;
GO

IF OBJECT_ID(N'dbo.technothirsty',N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.technothirsty;
END

SELECT s1.name INTO dbo.technothirsty FROM sys.all_columns AS s1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n1
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n2
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n3
CROSS JOIN (SELECT n FROM (VALUES(1),(2),(3),(4)) AS s(n)) as n4

CREATE CLUSTERED INDEX x ON dbo.technothirsty(name);
GO

DECLARE @name SYSNAME, @i INT=0;

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT name FROM dbo.technothirsty;

OPEN c;

FETCH c INTO @name;

WHILE @@FETCH_STATUS=0
BEGIN
SET @i+=1;
FETCH c INTO @name;
END

CLOSE c;
DEALLOCATE c

LOCAL keyword is used to make cursor local, by default cursor is Global, so cursor generally taking more time to execute.

As you have noticed that I have added LOCAL and FAST_FORWARD in cursor query. Lets run this query in sql server, I got execution time 25 seconds as below:

 

 Here are the techniques by which we can optimize execution time of cursor query.

Friends let me know if you have any better ideas to optimize.