|
|
Posted by Erland Sommarskog on 11/22/07 08:18
Hugo Kornelis (hugo@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, 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]
|