Tips to write a better TSQL Query for enhancing the performance of Stored Procedure - Part II

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1636 red flag
Rating: 5 out of 5  
 2 vote(s)

This is a series of article that will guide us how to write a better TSql query for enhancing the performance of Stored Procedure.This is the Part II of the series.In this series we will address the problems that a WHILE LOOP (AKA Pseudo Cursor) and a CURSOR pose and also a SET BASED approach as how to overcome them with pratical example will be demonstrated.
Recommendation
Read Find values from outside braces using T-SQL and XQuery before this article.

Introduction

This is a series of article that will guide us how to write a better TSql query for enhancing the performance of Stored Procedure.This is the Part II of the series.In this series we will address the problems that a WHILE LOOP (AKA Pseudo Cursor) and a CURSOR pose and also a SET BASED approach as how to overcome them with pratical example will be demonstrated. At the end, we will provide some links that will help the readers to get more insight into the problems that will be discussed here. Part I of this series can be accessed from here.

Rules to follow and Points to remember while writing a TSQL query

  1. 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.

  2. 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

References/Suggested Reading

  1. The Truth about "Cursor Busting" in SQL
  2. This is not the aggregate you're looking for
  3. Thinking a WHILE loop isn't a CURSOR

Conclusion

In this article, we have seen a proven fact (By WHILE LOOP and CURSOR) that SET BASED way of writing TSQL queries outperforms the Row By Row processing.Let's hit upon some more points at Part III. Stay tuned and hope you all have enjoyed the article.Thanks for reading

Recommendation
Read Find values within braces using T-SQL and XQuery after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)