From past few weeks, I have been taking interview for Sr. SQL DBA position in my team. What I found is, even candidates saying that they have expertise in Performance Tuning, have very wrong notion when it comes to Cursor Vs Loops in TSQL. This made me to write this blog post with some sample data showing that its not always that Loop is better than Cursor.
For this demo, I am going to use
But, first lets start with Set-Based operation. In this operation, we are processing about 500000 rows from dbo.Users table.
Implementing the same task with Cursor, it completed in 4263 milliseconds compared to 233 milliseconds of Set Based operations. This is no surprise. Set-based operations are always better that row-based operations.
Now, going for same work with While loop, it takes 5340 milliseconds compared to 4263 milliseconds of Cursor based query. Below is the screenshot for same.
Conclusion: It can be concluded that Loop based query is NOT always faster than Cursor until we have tested our code and scenarios otherwise. Implementing the Cursor with appropriate option can make it faster than Loops in many scenarios.
Please feel free to correct me by comments. If you find the article useful, do Like & Share 🙂