Home » Interview Q&A » Performance improvement of cursor SQL Server

Performance improvement of cursor SQL Server

Is Application running already in WPF
Convert DataTable to List C#

Hey folks till now we have discussed User defined functions in sqlKill Processes in sql server 2008, View in sql default constraint in sql , Remove Cross duplicate rows in SQLRecursive SQL Query , Recursive SQL Query-2STUFF and CONCAT in SQLRANK in SQL server , Difference between temporary table and table variable in sql serverUNIQUEIDENTIFIER in SQL ServerRAW Mode with FOR XML , AUTO Mode with FOR XMLEXPLICIT Mode with FOR XML , PATH Mode with FOR XMLOUTPUT Clause in SQL ServerDifference 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.

 

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

 

Cursor

 

Apply FAST_FORWARD to optimize query

 

 

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

 

Cursor-Fast-Forward

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

 

Apply LOCAL FAST_FORWARD to further optimization

 

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:

 

Performance improvement of cursor SQL Server Cursor-Loca-Fast-Forward 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.

About

Hello friends, My self Dhruv Sheth, I have more than 6 years of Industrial experience as a Software Developer. In my career I got chance to work with asp.net, c#,Web API, JavaScript, JQuery, WPF, WCF , Windows Application, PostScript, vb.net, GhostScript etc. So I have decided to share my knowledge with everyone and finally I have come up with TechnoThirsty.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Time limit is exhausted. Please reload CAPTCHA.




DISCLAIMER:
The content is copyrighted to technothirsty.com and may not be reproduced on other websites without permission from the owner.You may contact us using the information below.