I am breaking this article out into a few parts, only due to the length of the content. I don’t want to overwhelm you with tons of reading that you will not finish.
So, first things first:
Copyright Information:
This information on Cursors comes from “Inside Microsoft SQL Server 2005 T-SQL Programming” written by Itzik Ben-Gan. All examples on this page are taken directly from Chapter 3 of the book. Some of the examples have been modified only to eliminate processes that occurred in previous chapters of the book, such as creating test data in other tables.
Okay, now that that is out of the way, let us get to the meat of the article. As stated in my copyright information, this information is taken from Chapter 3 of the book. One thing I really like about Inside Microsoft SQL Server 2005 T-SQL Programming is that it provides great in depth knowledge on how things work. The one thing that I wish it had was the gory details on how things work.
Well, I am here to provide you with the gory details on cursors.
Overview
Now, one way to start an argument is to talk about coding standards in a room full of developers. If you ever get into this situation you know you will be spending a ton of time figuring out what is the best approach on your current argument. Eventually you will be arguing about cursors. Some people love them and some people hate them.
Cursors are something in SQL that can be a good thing and a bad thing and both at the same time.
The purpose of this article is to show you the good and the bad and also to show you how you can use set-based operations to avoid cursors. According to Itzik Ben-Gan the goal of his chapter on cursors is to “show you how to use them wisely”.
Why Avoid Cursors
Cursors conflict with the foundation of the relational model. When you use a cursor you are applying procedural logic versus using set-based logic.
This means that you write code with iterations and you focus on the “how” to deal with data.
Applying set-based logic you will typically, but not all cases, write far less code as you focus on “what” you want and not how to get it.
Why Use Cursors
Cursors have a lot of overhead involved with the row-by-row manipulation. You will need to calculate and estimate the cursor overhead of your query and make sure you cannot do your query with a set based operation before moving to a cursor. There are cases where a cursor will yield better performance then a set based operation. This article will show you how to see the performance differences between the set based operation and the cursor.
One important aspect of cursors is that they can request and assume ordered data as input. Queries can accept only a relational input, which by definition cannot assume a particular order. This is where the difference comes in when identifying the case where you may actually want to use a cursor as it might be faster. Examples of these are running aggregations and ranking calculations. The I/O cost involved with the cursor activity plus the cursor overhead might actually be lower then a set-based operation which does perform a greater amount of I/O.
What is the Cursor Overhead
For the most part, efficiently writing set-based solutions will outperform cursor-based solutions for two reasons:
- You empower the optimizer to do what it is good at, which is generating multiple valid execution plans and then choosing from the most efficient one. When you use a cursor-based solution, you are pretty much forcing the optimizer to go with a rigid plan that does not have much room for optimization.
- Row by row manipulation can create a ton of overhead. You can see that scanning a table with a simple query versus scanning with a cursor has a significant difference in the processing time.
Okay, so how do we go about “proving” that a cursor has more overhead then a set-based query? Well, we test it of course!
Continue to Cursors : An In Depth Look Part 2
