Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:
Maybe it is, but I doubt it. After investigating the effect of options,
I wouldn't be surprised to find a way to beat a cursor with the default
options, since they are SLOW - but I have yet to see a "poor man's
cursor" that outperforms a _properly optimized_ cursor.
Yes, just saying DECLARE cur CURSOR may lead to problem. I don't have
had much problems with performance on SQL 2000 and later, but that is
mainly because I was burnt enough on 6.5. These days I make my cursors
STATIC/INSENSITIVE as a matter of routine.
But another issue that I've seen people run into with dynamic cursors is
that rows keeps coming back to you, so that your cursor never terminates.
If your colleagues ever show you a way that they think beats a cursor,
please share it with me. Might make neat blog fodder.
Well, if you index your loop column it's a different matter, and while
have to admit that there have been occasions I've written that sort of
loops myself. But that is probably maninly due to that a cursor declaration
and all takes up some more space in the code. But from a performance point
of view, there is a certain overhead in setting up a static cursors, since
rows has to be copied. But if you have that many rows to make this overhead
noticable, then you are probably in dire need of a set-based solution.
Hmmm, maybe I'll do a sequel to the cursor episode anyway. The
misunderstanding is common enough to warrant some attention on my blog.
Yes, people see "don't use cursors" and then implement their loops in
a different way, and thus go from bad to worse.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx