Avoid WHILE LOOPS (AKA Pseudo Cursor) and obtain SET based methods.
In the case of While loops the rows are rows are processed one after another and hence if we want to submit any job(s) to the query engine, it process in a sequential manner.Where as if we can re-write the same query by using in a SET Based manner, we can submit a complete batch of job(s) to the query engine which on the other hand will be processed much faster way.
Let us prove our statement.We will generate a sequence of numbers from 1 to 1000000 (1 million) by using While loop and using SET based methods and will compare
Sequence number generation using WHILE LOOP
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();
--*********** THE QUERY BEGINS ************************
DECLARE
@i INT = 1,
@maxLimit INT = 1000000
WHILE (@i <= @maxLimit)
BEGIN
PRINT(@i)
SET @i += 1
END
--*********** THE QUERY ENDS ************************
SET @t2 = GETDATE();
SELECT
DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,
(DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;
/* Result */
ElapsedTimeInMilliSeconds ElapsedTimeInSeconds
40900 40.9
Sequence number generation using RECURSIVE CTE LOOP
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();
--*********** THE QUERY BEGINS ************************
DECLARE @maxLimit INT = 1000000
;WITH NumCTE AS(
SELECT Rn = 1
UNION ALL
SELECT Rn+1
FROM NumCTE WHERE Rn < @maxLimit)
SELECT *
FROM NumCTE
OPTION(MAXRECURSION 0)
--*********** THE QUERY ENDS ************************
SET @t2 = GETDATE();
SELECT
DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,
(DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;
/* Result */
ElapsedTimeInMilliSeconds ElapsedTimeInSeconds
12460 12.46
An improved version of generating the same number table (original credit goes to Itzik Ben-Gan) is presented below
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();
--*********** THE QUERY BEGINS ************************
DECLARE @maxLimit INT = 1000000
;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
NumCTE AS (SELECT TOP(@maxLimit) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM f)
SELECT *
FROM NumCTE
--*********** THE QUERY ENDS ************************
SET @t2 = GETDATE();
SELECT
DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,
(DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;
/* Result */
ElapsedTimeInMilliSeconds ElapsedTimeInSeconds
5246 5.246
We can figure out that, the SET BASED approaches outperforms the Row based approaches.
Avoid CURSORS and obtain SET based methods.
In the case of CURSORS the rows are rows are processed one after another.This consumes more memory and create page/row locks.To elaborate more on the point, the moment a cursor opens it loads a chunk of rows into memory and locks them.This creates a potential block.Then as we loop through the cursor we are making changes to other tables, performing some operations like insert/update/delete and still keeping all of the memory and locks of the cursor open.This cause the performance issues.Where as if we can re-write the same query by using in a SET Based manner, we can submit a complete batch of job(s) to the query engine which on the other hand will be processed much faster way.
Quoting the SQL Team
Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row. In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.
To substantiate the above statements, we will perform a simple experiment
DECLARE @t1 DATETIME
SELECT * INTO #temp
FROM master..spt_values
--*********** THE SET BASED QUERY BEGINS ************************
BEGIN TRAN
SELECT @t1 = GETDATE()
UPDATE #temp
SET number = 0
SELECT
DATEDIFF(millisecond, @t1, GETDATE()) AS ElapsedTimeFromSetBasedInMillis;
ROLLBACK
--*********** THE SET BASED QUERY ENDS ************************
--*********** THE CURSOR BEGINS ************************
BEGIN TRAN
DECLARE @name VARCHAR
SELECT @t1 = GETDATE()
--Declare a cursor and loads the rows/data into the cursor memory
DECLARE tempCursor CURSOR
FOR SELECT name FROM #temp
-- Open the cursor
OPEN tempCursor
--Loop thru each row for processing
FETCH NEXT
FROM tempCursor
INTO @name
--Do something interesting
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #temp
SET number = 0
WHERE NAME = @name
--Repeat the process for accessing the next row available in cursor memory
FETCH NEXT FROM tempCursor
INTO @name
END
-- All operations completed.Now close the cursor
CLOSE tempCursor
-- Clean up
DEALLOCATE tempCursor
--*********** THE CURSOR ENDS ************************
--Measure the cursor processing time
SELECT
DATEDIFF(millisecond, @t1, GETDATE()) AS ElapsedTimeFromCursorInMillis;
ROLLBACK
--Clean up the table
DROP TABLE #temp
The result
There are however some ways to improve the cursor performance.Interested readers can look into Performance Tuning SQL Server Cursors for the same