|
CHAPTER 10
Programmable Objects
349
1.
First and foremost, when you use cursors you pretty much go against the relational model,
which is based on set theory.
2.
The record-by-record manipulation done by the cursor has overhead. A certain extra cost is
associated with each record manipulation by the cursor when compared to set-based manipu-
lation. Given a set-based query and cursor code that do similar physical processing behind the
scenes, the cursor code is usually many times slower than the set-based code.
3.
With cursors, you spend a lot of code on the physical aspects of the solution—in other words,
on how to process the data (declaring the cursor, opening it, looping through the cursor
records, closing the cursor, and deallocating the cursor). With set-based solutions, you mainly
focus on the logical aspects of the solution—in other words, on what to get instead of on how
to get it. Therefore, cursor solutions tend to be longer, less readable, and harder to maintain
compared to set-based solutions.
For most people, it is not simple to think in terms of sets immediately when they start learning SQL.
In contrast to thinking in relational terms, it is more intuitive for most people to think in terms of
cursors—processing one record at a time in a certain order. As a result, cursors are widely used, and
in most cases they are misused; that is, they are used where much better set-based solutions exist.
Make a conscious effort to adopt the set-based state of mind and to truly think in terms of sets. It can
take time—in some cases years—but as long as you’re working with a language that is based on the
relational model, that’s the right way to think.
Working with cursors is like fishing with a rod and catching one fish at a time. Working with sets,
on the other hand, is like fishing with a net and catching a whole group of fish at one time. As another
analogy, consider two kinds of orange-packing factories—an old-fashioned one and a modern one.
The factories are supposed to arrange oranges in three different kinds of packages based on size—
small, medium, and large. The old-fashioned factory works in cursor mode, which means that conveyor
belts loaded with oranges come in, and a person at the end of each conveyor belt examines each
orange and places it in the right kind of box based on its size. This type of processing is, of course,
very slow. Also, order can matter here: If the oranges arrive on the conveyor belt already sorted by
size, processing them is easier, so the conveyor belt can be set to a higher speed. The modern factory
works in a set-based mode: All oranges are placed in a big container with a grid at the bottom with
small holes. The machine shakes the container and only the small oranges go through the holes. The
machine then moves the oranges to a container with medium holes and shakes the container, allow-
ing the medium oranges to go through. The big oranges are left in the container.
Assuming you are convinced that set-based solutions should be your default choice, it is important
to understand the exceptions—when you should consider cursors. One example is when you need to
apply a certain task to each row from some table or view. For example, you might need to execute
some administrative task for each index or table in your database. In such a case, it makes sense to
use a cursor to iterate through the index or table names one at a time, and execute the relevant task
for each of those.
www.it-ebooks.info
350
Microsoft SQL Server 2012 T-SQL Fundamentals
Another example of when you should consider cursors is when your set-based solution performs
badly and you exhaust your tuning efforts using the set-based approach. As I mentioned, set-based
solutions tend to be much faster, but in some cases the cursor solution is faster. Those cases tend
to be calculations that, if done by processing one row at a time in a certain order, involve much
less data access compared to the way the version of SQL Server you’re working with optimizes cor-
responding set-based solutions. One such example is computing running aggregates in versions of
SQL prior to SQL Server 2012. I provided a very efficient set-based solution to running aggregates in
Chapter 7, “Beyond the Fundamentals of Querying,” using enhanced window aggregate functions
in SQL Server 2012. However, if you’re using an earlier version of SQL Server, set-based solutions to
running aggregates don’t get optimized very well; they involve multiple scans of the data. Optimiza-
tion is outside the scope of this book, so I won’t go into detail here; all you need to know here is that
cursor solutions to running aggregates involve only one scan of the data, and therefore can be faster
than set-based solutions on pre-2012 versions of SQL Server.
In the chapter’s introduction, I mentioned that I’ll provide a high-level overview. Still, an example
of cursor code is probably appropriate here.
Working with a cursor generally involves the following steps:
Do'stlaringiz bilan baham: | 
