|
|
Posted by Erland Sommarskog on 07/27/07 09:28
Simon Barnett (sb@simonnospambarnett.com) writes:
> Are cursors deemed as bad practice? Everyone I have spoken to says to
> avoid them.
It's better to say: they are rarely the right tool for the task. But
sometimes they are.
First of all, it's not the cursor as such that is bad, it is the looping.
I say this, so that next time you have to develop solution that requires
looping, you don't go and implement the loop without a cursor. That is
usually even worse.
So when is looping and cursors the right thing:
1) There is a stored procedure that accepts scalar input that you need
to call, and re-writing it to handle set-based data will cost you more
than the performance degradation. That is, the stored procedure is
very complex, or a system procedure, like xp_smtpmail.
2) You process data from some untrusted source, and if there are errors
in the data, you don't want the entire processing to fail, just the
bad rows. Doing this set-based, requiring doubling all constraints,
trigger checks, so you may be prepared to take the performance hit.
3) When they give better performance. The reason cursors in the very
most cases are a poor solution is that the performance is magnitudes
worse than a set-based solution. But there are operations where a
set-based solution do not perform well, often because the language
lacks a way of expressing them. Two such examples are numbering rows
in the output, or include a running sum in the result set. Doing this
with set-based in the proper way in SQL 2000 for a large set of data,
gives outrageous performance, which a cursor easily beats. SQL 2005 adds
new syntax that makes the row-numbering affair very quick. But the
syntax for running sums is still missing, although it's defined by
ANSI.
And possibly 4) The logic is very complicated, and it can be easier to
express it in traditional programming terms, even for a seasoned SQL
developer, and the reduced development cost outweighs the performance
cost for the cursor.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|