Gary (ga**********@w cc.ml.com) writes:
My experience is that using a WHILE loop, if applicable, is generally
faster than using cursors.
I rewrote a coworkers cursor code that did a simple concatenation of a
column across groups. It took about 15 minutes to perform before with
a cursor, and with the WHILE loop it took 12 seconds.
In some/other cases, I'm sure WHILE would not work better.
SQL Server cursors are notoriously inefficient. I've seen examples
where people have rewritten cursor code to pull a data into a c#
program via ADO.NET, process the records, and write them back to the
database. It was about ten times faster than equivalent cursor code.
My only guess as to why cursors are so inefficient is that they are
interpreted, not compiled code.
The default cursor type in SQL Server is keyset-driven. I've never
understood what this means, but I always add INSENSITIVE before the
cursor name. Not doing this, have sometimes given me horrible query
plans for the query in the cursor. (This was in 6.5, but I'm not
going to try in SQL 2000.) INSENSITVE means that the cursor set is
fixed and copied to some work area.
I maintain that if you need to iterate - and sometimes you do - then
a cursor is the best way to go. I have caught several of my colleagues
red-handed when they've gone for things like:
WHILE @id IS NOT NULL
BEGIN
SELECT @id = MIN(id) FROM #tmp WHERE id = @id
IF @id IS NOT NULL
BEGIN
SELECT @this = this, @that = that ... FROM #tmp WHERE id = @id
And I've caught them when their code has caused performance issues,
because the temp table had more rows than they had anticipated, and
they had not indexed the id column. Well, with a cursor they would
not have needed the index anyway.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
