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.
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 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.