January 15, 2007 at 10:32 am
I decided in the end that nobody was going to come up with an answer to my previous question about the performance difference between a cursor-based procedure, and one based on 'While' Loops.
I've just tried it out with a large table, and my finding is that there is no difference in performance between them.
I'm not sure what to conclude, beyond shrugging and muttering that there is no justification for using a cursor over a simple 'while' loop on performance grounds, though it did rather surprise me what a large difference a well-chosen index made in the 'While' solution. The 'cursor crew' don't have to worry about that.
Best wishes,
Phil Factor
January 15, 2007 at 10:48 am
Thanks for trying it out, Phil. I suspect that this comparison depends greatly on what type of cursor is involved, and the locking and concurrency implications (for example, is the cursor driving the process a fast forward-only cursor? a static cursor? dynamic?). Likewise, is the loop encapsulated in a single transaction, or does it just 'drive' repeated, completely separate transactions?
So, if I may be permitted to push that argument a little further, I wonder if people who have seen a dramatic performance changes in replacing a cursor with a while loop have not in fact inadvertently changed one of those other factors, and in reality the difference was not in the change from a cursor-driven loop to a while-driven loop, but instead in the underlying change of locking behavior.
I'd expect a while loop to run about at the same rate as a fast forward-only cursor, because it amounts to the same thing. (I tend to write those very rare things that actually need looping with while instead of a cursor, but just out of style preference.)
January 15, 2007 at 10:55 am
I think thats a good point Merrill. Cursors can do a lot of work for you if you need them to, managing keysets or the entire table in tempdb so it doesnt move. I think I also agree that while loop will do as a cursor in many cases (I've seen cases where they were re-evaluating the select on each loop, making it worse than many cursors)
January 15, 2007 at 12:33 pm
When I first started out I was strongly advised to avoid using cursors/WHILE loops at all costs. I still agree with that a great deal, but they're a tool. Like all tools they have their place.
That's not saying that they're not highly overutilized.
I had a situation about a year ago where one system was feeding in data which came in the format of a very long nvarchar string in a single column. That string was an array delimited by semicolons which needed to be split into standard table columns. All the examples I found online for this sort of thing recommended using looping procedures.
Thinking myself awfully clever, I decided to use a CTE to strip them out in one glorious recursive run. It worked wonderfully.
When testing it, I wanted to be able to justify how much better it was than using a WHILE loop. I found that while the CTE was effective for smaller data sets, performance dropped off dramatically as larger and larger numbers of records needed to be processed. There was a lot of trouble with scalability. Using a WHILE loop had a more steady cost as the size of the data increased. The data we needed to process was consistently about 100,000 records or so higher than where it needed to be to use the CTE. I tweaked and optimized trying to eek out the performance to justify my belief that anything could be better than looping but it just didn't pan out. We tried a number of other approaches as well but nothing could touch the performance we ended up getting with the loop.
In the end the WHILE loop worked out best for what we needed to do and with proper care to avoid the common pitfalls associated with that approach it was a good solution. I've been known to fire off a "you should never use a cursor" admonition from time to time, and that's still what I recommend, but in reality you should pick the best tool for the job.
January 15, 2007 at 12:34 pm
A good solution meets the requirements (does the job), is reliable and can be maintained. Adequate performance is a part of doing the job, but if a cursor is simpler and it works - fine.
Come on folks, IT solutions are there to meet business needs, not pander to a developers sense of what is or is not elegant.
January 15, 2007 at 3:44 pm
Sorry .. bit slow on picking up the replies .. ok cursor vs while -- what i found was the while tended to bind the whole process into one transaction whilst each loop of the cursor was a transaction ( without adding transaction commands )
So I'll pose a simple "cursor" question. I have 50 databases on my server, the quickest way to back these up is to back them up sequentially ( I only have one data and one backup drive/array ) [ Now I know that sequential backups is way quicker than any other method - too many years in controlled environments ] so how do I apply set based processing to a sequential operation? In my world a while = a cursor, oh and I don't use sql maint plans and the sp_msforeach procs are only cursors in disguise too!
Just interested ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 15, 2007 at 3:53 pm
Colin,
50 step job, one for each database.
January 15, 2007 at 3:53 pm
"I decided in the end that nobody was going to come up with an answer to my previous question about the performance difference between a cursor-based procedure, and one based on 'While' Loops."
A well written while loop is a few percentage points slower than a fast forward read only cursor. The while loop is slightly faster than all other cursor options. Other than that, it depends.
Thanks for the great article, Andy. I appreciate your pragmatism. I think your third reason, to re-use a single row proc) is a good reason. I've done this myself, wondering whether it was a valid design option or not. Nice to get the OK from a guru.
That said, I'd also agree that cursors can virtually always be avoided, as long as you are accessing a single database. It's just whether you want to take the time to do it. And it's certainly better to re-use the business logic in a single row proc than to duplicate that logic in a cursor as well. As far as code maintainability is concerned, having your logic in ONE PLACE is worth the added overhead.
Also, I'd add another reason for looping, and that's to throttle large transactions.
cl
Signature is NULL
January 15, 2007 at 4:16 pm
Andy,
Very glad to see someone standing up for cursors. They are a great tool that is easily misused. I am the senior architect for an R&D company which, needless to say, often requires some very complicated SQL. I find cursors most often useful for tasks such as, for instance, querying the system tables, looping over the results and building dynamic SQL into a varchar(8000) to exec(). For this type of work, they are *ideal*.
I can post sample scripts using this technique that do things such as rebuilding all primary keys in a database and make them the clustered index, or say, deleting a column from a table and from all other tables that contain a FK to that column.
January 15, 2007 at 4:29 pm
This is one of the very few cases where a sequential solution can perform better than a set based solution in T-SQL. For most set based 'running total' solutions, the number of reads required increases exponentially with the number of rows being processed (This can also apply to the recursive CTE Aaron Ingold mentioned).
Compare this to a cursor solution (or while loop), which usually requires a single scan of the data, resulting in a linear increase in the number of reads. Even taking into account the cursor overhead, as the number of rows increases, there will always be a point where the cursor becomes more efficient.
This just shows there is no hard and fast rule regarding cursors, and that each problem needs to be assessed individually. Unfortunately, as many people have mentioned, time constraints often make it difficult to develop and test multiple solutions to a problem.
January 15, 2007 at 4:46 pm
Thanks again to all who posted (or will post). Its a good thread! I'll plan to do a follow up to compare the various cursor options to while loops. I dont expect to find anything new, but it will be a nice follow up and heck, maybe I can convert someone to my view point yet:-)
On a more humorous note, I haven't seen anywhile suggest GOTO over the while loop? Is GOTO really dead? No, Im not going to advocate for that one.
January 15, 2007 at 4:54 pm
I'm not an advocate of the almost forgotten GOTO, but I have seen it in of all places, vendor supplied SPs. Mind you, these are not Mom'n'Pop vendors either. Some have rather prominent initials and logos !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 15, 2007 at 6:07 pm
Great article Andy! And what a great discussion, too.
I've used cursors once in a while when they were the best solution, most often when working with a poorly-constructed application, to match the vendors logic (or illogic).
There is a way to avoid cursors sometimes. Imagine that you need to perform a sequential task on some records, with ID as the PK...pseudo code:
1. select ID into #temp from TheTable where (some criteria)
2. select @var = top 1 ID from #temp
3. perform the task where ID = @var
4. delete from #temp where ID = @var
5. if select count(*) from #temp > 0 then loop
January 16, 2007 at 1:06 am
Temp tables are the way to go when you need to step through data.
And, in-fact when every possible, I like to create real tables that are dbo. or user owned and live though many transactions, each row having a simple StatusID 1=need to do, 2=doing, 3=done (instead of deleting as you go as was suggested), then when your done stepping through the whole table, and nothing is a status 1 or 2 they you can drop the table or keep it around for performance anaylisis or what ever. It's faster than a cursor, and you can "See" what's going on while it's running or- even more importantly see what whent wrong when something breaks.
January 16, 2007 at 1:28 am
Jeff - a 50 step job is not the answer as you will have to alter the job to cope with additions or subtractions and you'd still need a loop to code the job, and not wishing to be rude that's exactly what I think Andy's article was about - going to extreme lengths to avoid a loop/cursor, a 50 step job ( although I do agree it is a solution ) vs a couple of deployed procs which do the work for you!
William - surely to work through the temp table you'd need a loop, either a while or a cursor ? ( as we don't have a for next or repeat until ( which are still loops )
Joe - I never thought about using a goto , I will have to examine that route! Many many years ago when I was learning programming languages I followed pascal as i was advised this was the way to go to learn structure etc., so I've never used a goto -yet < grin >
e.g. this is how I do a sequential backup of databases ( there's some other code which reads from sysdatabases into a temp table.
set @count=1
WHILE @count<=(select max(NumKey) from @NameTable)
begin
select @dbname='['+dbname+']' from @NameTable where NumKey=@count
EXEC proc_BackupDatabase @dbName
set @count=@count+1
end
--endwhile
Now I use a while, but it could be a cursor, but it's still a row by row so, cursor = while, so my question remains , how to do this set based + how much time to find another solution vs my cost to an employer as a contractor?
I just wanted to also remark that putting cursor ops in middle tier doesn't remove the cursor - in fact I'm sure one of the mcdba questions is exactly on where to process a cursor, server or client/middle tier. A row by row operation is just that regardless of whether it's written in vb, client, n'tier, while loop, serverside. In fact with 2005 and the CLR Jim Gray suggested that the middle tiers could be placed into the server - I'm waiting for my first contract to "tune" the missuse of the CLR, which I'm sure will happen!
I much prefer the "It just depends" approach.
Andy - but doesn't the subject cursor stir up a storm ? just like dynamic sql vs procs .. heh heh!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 31 through 45 (of 81 total)
You must be logged in to reply to this topic. Login to reply