Posted by Erland Sommarskog on 11/21/07 22:22

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> If performance does matter and you still want to use a cursor, than you
> might wish to read my recent blog posts on the performance effects of
> various cursor options. You can find it at
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-
> optimization-options.aspx

As the hour is late, I didn't have the time to read it all, but I will
save that for later. But I found the conclusions interesting. I always
go for STATIC cursors myself, mainly because then I know that nothing
unexpected will happen. (Actually I really prefer INSENSITIVE as that is
ANSI, but alas it cannot be combined with LOCAL.)

I also note that WHERE CURRENT OF is still bad. I tried WHERE CURRENT OF
when I first got play with SQL 6.0, but ran into permission issues, and
I also noticed a scan in the query plan. And since updating through the
PK is dead simple, why bother with anything else?

One battle I have to fight in my shop is with colleagues who think that a
"poor man's cursor" is better. And maybe sometimes it is. But having a
loop where you do SELECT MIN() on a non-indexed temp table with 100000
rows is definitely not.


--
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

[Back to original message]