December 24, 2008 at 8:44 am
Now if you REALLY knew how to do this you would have realized that you made it run slow on purpose. The modified code below only hits the clustered index of sysdatabases. Your query has to build a table on the fly just like the bad cursor.
I added one word "fast_forward" to get your cursor code to run faster than your memory table code.
Lesson: Don't blow-off things you don't fully understand.
Lesson to SQLServerCentral: learn to edit better.
declare goodCursor Cursor fast_forward
for select name from sysdatabases
where name not in ('master', 'model', 'msdb', 'tempdb')
open goodCursor
fetch next from goodCursor into @dbname
while @@fetch_status = 0
begin
select @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
exec(@query)
fetch next from goodCursor into @dbname
end
close goodCursor
deallocate goodCursor
go
December 24, 2008 at 8:45 am
I like the article, but would like show a variation that will produce the same output, with the advantage to be able to do other controls if necessary (instead of deleting records from a temporary table). See the code below, where the queries use the main table - as opposed to a temporary table (@memory-table or a #tempdb-table):
------------------------------------------------------------------------
declare @query varchar(100),@dbname sysname
select
@dbname = min(name)
from
sys.databases
where
name not in ('master', 'model', 'msdb', 'tempdb')
while (@dbname IS NOT NULL)
begin
select @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
--exec(@query)
print @query
/*
-- here you'd place an update statement accordingly to your business rules or
-- to update statisticts, status, etc
update
tablename
set
field = ...content...
where
name = @dbname
*/
select @dbname = min(name)
from sys.databases
where
name not in ('master', 'model', 'msdb', 'tempdb')
AND
name > @dbname
end
go
December 24, 2008 at 8:47 am
Lot of good responses here. I have to say, I don't like seeing lopsided articles about getting rid of cursors. They exist still partially because there are good uses for cursors. There are so many of these types of "Cursors are Evil" articles that SQL noobs see these and kill millions of brain cells trying to avoid using cursors. I do think cursors are over used a bit but I would challenge anyone to accomplish some of the beastly data migrations that I have had to do without a cursor. A balanced article would be more appropriate. Not everyone that reads these articles has years of SQL experience. This site shows up well on search results and lots of new SQL developers and DBA's show up here for answers. My rant on onesided articles is done now.:D
December 24, 2008 at 9:05 am
Actually in most cases the best alternative is a loop written in a modern language such as C#, not a T-SQL WHILE loop or a CURSOR. Not only you have the advantage of using a convenient modern language, you may get better performance too. I believe that all these WHILE loop vs. CURSOR debates are becoming irrelevant...
December 24, 2008 at 9:10 am
dford (12/24/2008)
Lot of good responses here. I have to say, I don't like seeing lopsided articles about getting rid of cursors. They exist still partially because there are good uses for cursors. There are so many of these types of "Cursors are Evil" articles that SQL noobs see these and kill millions of brain cells trying to avoid using cursors. I do think cursors are over used a bit but I would challenge anyone to accomplish some of the beastly data migrations that I have had to do without a cursor. A balanced article would be more appropriate. Not everyone that reads these articles has years of SQL experience. This site shows up well on search results and lots of new SQL developers and DBA's show up here for answers. My rant on onesided articles is done now.:D
My apologies if it seemed lopsided. So many scripts and articles on this and other web sites are ubiquitously populated with cursors, I thought about giving an opposite approach.
A couple things I've learned:
1. First time article, I'll be prepared next time with more thorough examples.
2. People get the most passionate about those things you expect to elicit the least amount of emotion.
Have a good holiday folks, and a happy new year.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
December 24, 2008 at 9:19 am
Thanks Gaby, You've been a good sport, and it has been a great discussion. I bet your next SSC article will be wonderful. My first one was ghastly.
Happy Hols to you all
Best wishes,
Phil Factor
December 24, 2008 at 9:27 am
Gaby A. (12/24/2008)
My apologies if it seemed lopsided. So many scripts and articles on this and other web sites are ubiquitously populated with cursors, I thought about giving an opposite approach.A couple things I've learned:
1. First time article, I'll be prepared next time with more thorough examples.
2. People get the most passionate about those things you expect to elicit the least amount of emotion.
Have a good holiday folks, and a happy new year.
One important thing I left out of my response. Gaby A., you were brave enough and took the time to write an article for publication! That alone deserves big credit. It's easy to poke at someone else's article. Sounds like this one has been a learning experience for you so you got something out of it that is valuable.
Keep it up! and thanks for the reply.
December 24, 2008 at 10:28 am
I agree with the other's. Hat's off to Gaby for taking on a highly controversial subject.
As other's have also stated, running DBCC and certain system sprocs is one of the few places where either a cursor or a while loop will suffice simply because of the way they need to be executed (although I've rewritten things like sp_SpaceUsed to be both set based and return a single result set). Used properly and for only certain things, a good read only, forward only "firehose" cursor will do the trick nicely especially for database maintenance...
... the problem is that people frequently use cursors to process rows in a table instead of using correct set based methods. The reason why so many people are against cursors is two fold... one reason is because of the locking they put on tables unless they are declared as "firehose" cursors. A good firehose cursor is no more intrusive than a temp table/while loop combo. The other reason is, and most people forget this... cursors have while loops. What I'm saying is that one is just as bad as the other and with very, very rare execeptions, have no place in an RDBMS because they're slow compared to set based.
I just won't have the damned things in my database because of a 3rd reason... troubleshooting. I've found that the use of temp tables makes troubleshooting a lot easier than the use of a cursor because they can be examined between runs in QA or SMS as the session stays open there.
The bottom line is, if you're using a While loop, whether it be in a cursor or not, to process individual rows rather than to control whole processes or separate sets of information, you've probably done it wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2008 at 10:41 am
I am not sure if a table variable (a memory copy of data) would be any more effcient than a SQL memory copy of the data e.g. "cursor" especially if it read-only. Let us not forget that with a cursor you can also update the real data table - not all work is read-only and have the ability to FIRST, NEXT, LAST, PRIOR, ABSOLUTE and RELATIVE functions.
December 24, 2008 at 10:45 am
They'll have to pry the cursor from my cold, dead fingers...:w00t:
~BOT
Craig Outcalt
December 24, 2008 at 10:46 am
jacroberts (12/24/2008)
Jeff Moden (12/24/2008)
Good idea, Gaby, and good article...Could you list one thing that's a good idea in this article, spelling mistakes aside?
Nope. I like people who try.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2008 at 10:50 am
It may be time to stop considering cursors a crutch for the procedurally oriented. This sidesteps the question of how to replace them, or the question of why one should replace them.
The database vendors are rewriting their cursor implementations - a good thing since most "SQL Servers" aren't used to deploy true relational databases.
I personally enjoy working with data organized to take full advantage of the elegance of the relational model, but there's very little of that being done - and even so, usually an application needs to do row/by/row processing of some kind, whether you do it by loading a hasharray in Perl or use a cursor on the server.
Sybase 15 now has several kinds of cursors depending on the way you want to move them, and if you choose the appropriate one they do not have the usual issues cursors bring. SQL Server 2005 and 2008 have shown Microsoft also going in the direction of "why call X Y or Z a bad practice if we can make it work well?"
This makes a lot of sense. Since, in theory, database designers and programmers should not be considering the physical implementation (that's what the database engine is for), neither should the database engine be busy trying to enforce certain programming styles by retaining penalties for "inefficient" expression of work. Getting the results, in an acceptable time, is the database engines job. Specifying the results is ours.
Sometimes I feel like I am turning my back on tradition when I tell our programmers it's OK to do things that I've been against for years. I used to tell them to break up 12 way joins - which is terrible advice now.
Roger L Reid
December 24, 2008 at 11:21 am
Matt Whitfield (12/24/2008)
@jacroberts -I didn't get any faster results out of the firehose than I did out of the select-only method. I ran it a few times, they came out pretty much on par.
@jeff -
I tried to test the COALESCE method, but it was horrifically slow over about 10,000 rows. Also interested why you say SELECT COALESCE(@SQL .... when it's actually a lot quicker to just set @SQL to '' first?
@Gaby -
Next time - do the performance tests!! Proving the concept of a while loop isn't necessarily of great value!
@All -
My personal draw from this thread is that yes - you should avoid cursors. Why? Because if you are using a cursor you are probably engaged in a RBAR style operation, which is going to kick you in the *** when you have to do it over a lot of rows.
HOWEVER
If you must use an RBAR for whatever reason, then an appropriately configured cursor will perform on a par with an appropriately configured while loop for a small number of rows. For a larger number of rows, the performance scaling is more linear with a cursor. So just stick to the cursor if you have to do something RBAR style, and stop trying to invent new ways to do the same thing - because doing that just shows that you have not understood *why* cursors are bad.
I'm out - it's Christmas!!!
Matt;
Great post! My view on this is if cursors were so bad then why does the construct even exist? Cursors have their place in the DBA world. I have written very complex billing processes that were best handled using a cursor. Performance wasn't an issue with this process but the ability to wrap my head round the requirements, to which there were many, and to insure that it handled the requirements correctly.
I've also spent a fair amount of time optimizing code which used cursors. I made the discovery that the dreaded cursor some times often produces the fastest results. What I found in a number of cases that I was able to get as much as a 50-60% improvement in performance of a cursor if it was constructed properly.
My view is enough with the cursor bashing. They do the job they are set out to do. If it is performance you are looking for and you have squeezed all the performance out of a cursor you possibly can, then look to design the code other ways.
One last note... I will almost never use a cursor driven procedure that is going to be in the part of a mainstream system. Beyond that I opt to use the style of my choosing.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 24, 2008 at 11:29 am
Hello All,
The Cursor question has always been a very interesting topic with SQL development.
In the discussions all the comments are related to a specific task, running a loop in order to process records and which method would perform best speed wise.
I think a more important question is which method mentioned would have better or more adverse effect on any other process/Applications running on the server using the specified tables, etc...
If a cursor or while loop takes a little more time but has less adversity to the rest of what may be going on, wouldn't that better? Does anyone have any ideas on that? I would be very interested to know your thoughts.
Thank you and Happy Holidays.
December 24, 2008 at 12:17 pm
Kurt W. Zimmerman (12/24/2008)
Matt;Great post! My view on this is if cursors were so bad then why does the construct even exist? Cursors have their place in the DBA world. I have written very complex billing processes that were best handled using a cursor. Performance wasn't an issue with this process but the ability to wrap my head round the requirements, to which there were many, and to insure that it handled the requirements correctly.
I've also spent a fair amount of time optimizing code which used cursors. I made the discovery that the dreaded cursor some times often produces the fastest results. What I found in a number of cases that I was able to get as much as a 50-60% improvement in performance of a cursor if it was constructed properly.
My view is enough with the cursor bashing. They do the job they are set out to do. If it is performance you are looking for and you have squeezed all the performance out of a cursor you possibly can, then look to design the code other ways.
One last note... I will almost never use a cursor driven procedure that is going to be in the part of a mainstream system. Beyond that I opt to use the style of my choosing.
Kurt
Lot's of people feel that way. One of the reasons why you don't think there's a performance issue with the "very complex billing process" is because no one has written it using proper set based techniques and it may not have gotten big enough, yet, to become a problem. The folks at my previous company made the very same claim with their "very complex billing process" and it worked fine until they hit the tipping point... suddenly the procs that were taking just an hour or two started taking more than 16 hours to complete.
So far as making the discover that cursors are faster than good set based, the answer is "not ever"... the problem is that a lot of stuff that looks set based actually isn't. Recursive CTE's and triangular joins (ie. running total problem, etc) are classic examples of things that look set based but aren't and they usually have performance much worse than a cursor.
Also, set based does not mean "all in one view" or "all in one query", yet lot's of folks try to do that and then wonder why their supposedly set based code is so slow.
"Cursor Bashing" is actually a very good thing because most people just don't know when a cursor (or while loop) is actually appropriate... they give up too easily on finding the correct set based, scalable, high performance solution.
I agree that cursors were put there for a reason... but performance of code isn't one of them... there is no "squeezing" of performance out of cursors.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 272 total)
You must be logged in to reply to this topic. Login to reply