Cursors - Are they always the wrong way
-
Imagine the scenario of an Order placed with a supplier in which there are several Product Items in the Order.
When the Order is placed a number of other tables are updated to reflect the fact that items are expected for delivery. Totals for products expected as well as those delivered or in use must be maintained to assist in keeping a knowledge of stock levels in different locations - hence the extra tables.
However, not all Order Items may be fulfilled at one time.
Got the scene?
Well, being a ludite I believed the best approach would be to update the status of the order as per the event (Order placed, Order Received etc...) and then iterate through the Order Items, interrogating the Order Item Status and calling sp's to deal with the other tables to be updated.
I am expecting between 1-20 order items in each order so not a massive number of rows. Hence, I thought a cursor would be ok. However, I got hit by the LOCAL/GLOBAL Cursor scope problem. (It is a multi-user environment and just happened to have two users call the sp at the same time and the Cursor was not declared as LOCAL, so ***BANG***. See :-
EXEC sp_dboption 'DBName', 'default to local cursor'
So now I have LOCAL, multiple executions of the sp are fine)
Anyway, back to the plot. Am I better off executing some weird and wonderful SQL Statement that only a rocket scientist can understand to reflect the changes in the other tables, or am I sensible to approach it in the time-honoured linear fashion?
I'm buggered if I know.
Any words of wisdom would be greatly appreciated.
-
There is probably no right answer to this.
In general, set based processing is usually quicker than individual row based processing. Nobody would dream of count the rows in a table by looping through them one by one, they would use select count(*)...
With you 1 - 20 rows, you probably wouldn't see much difference in performance (at the client end) between the two. I use cursors and can process 1,800 rows per second - cursors aren't all bad.
IMHO, easy to read code is more important the out and out speed. I'm sure that everyone has seen code that takes them a few days to work out what is going on. Add up the cost of the programmer each time it happens and you can easily justify the extra cost of faster processors.
Sometimes, you need to do individual record processing. If you need to process different logic on each row (e.g. is the item in stock - yes then dispatch item/no then put into holding basket) then cursors are perhaps the best way of processing.
If it works, and everybody is happy (especially with performance) then there is no reason to change it.
That's my opinion but no doubts others will have other opinion.
Jeremy
-
I agree with Jeremy, there is no right or wrong with this type of question. I use whatever gets the job done with a reasonable balance between easy code and performance. I have approx 30 procs that run daily that use cursors on data sets of up to 10K rows, they run fine. In the past I have spent days trying to create set based queries but gave up and used cursors.
Too many people give cursors a bad name, but why? It is an option to use when necessary, not an option to be avoided at all costs.
Why cut off your nose to spite your face!!
Far away is close at hand in the images of elsewhere.
Anon. -
I totally agree with both Jeremy and David.
Cursors may not be liked by the 'purists', but they are part of the SQL language, so why shouldn't you use them. Especially if performance is not a problem, and using a cursor results in easier to read and easier to maintain code.
I have occasionally tried to use a set based alternative to the cursor, only to find a small change required later on has meant re-writing the code to use a cursor anyway.
After all, there aren't that many rocket scientists about.
-
I'm not being daft then.
I cant understand why people go to such lengths to avoid using a technology / method that has been around for decades (almost). Just coz it's not objective or set based people believe it must be wrong. Surely there is merit in doing things that other human beings can understand.
Remember Recursion. People thought it was clever to use it until some poor git had to solve a problem in the code.
-
I will say I am not a fan of cursors for server side stuff just by the way they use memory as they are more intensive for the most part. However I have written and rewritten queires that as a cursor take hours to complete as set based work within minutes. But conversely I have tested set based methods that took longer than cursors. It boils down to can you accomplish your goals set based with better performance or cursor. However, you may not be a rocket scientist and not see a valid set based solution so you look at a cursor instead which is just fine. But set based or cursor always keep in mind that there may be better ways to do things and if you come across something new or just to increase you knowledge yourself use profiler to get the poorest performing queries and rewrite them over and over until you cannot think of another option. Implement what you know and can get the work done but always come back and streamline when you get a chance.
-
I believe that everything (especially in SQL) is an option. Write and re-write the same procedure as many ways as you can think of and test it for speed. Nothing is really a "bad practice" when the worst idea could work best in a certain situation. It all depends on the situation and db structure. I have always avoided cursors as much as possible for the simple fact of trying to learn the set based way of thinking.
I started out as an Access db query maker, which we all know is point-click and for the most complicated problems I would build a small client app that would loop through all the records that needed to be interrogated through. Then we moved on to SQL and I was introduced to the cursor, I thought it was the best thing since sliced bread until I read an article suggesting to avoid the use of cursors.
Bottom line, I use them in situations that I feel need a cursor but it's all personal preference, I am sure we don't all use the same naming conventions.
-
I agree with almost all of that. In practice (in my environment anyway) I expect 90-95% of our code will be set based. Nothing wrong with using a cursor when it's the right tool for the job, just don't use it because you don't want to see if there is a way to figure out a set based solution. The most common use of the cursor I see is in a trigger to support multiple row transactions, where each row will result in a proc call (so that we get code reuse).
Andy
-
Bingo!
Code Reuse!
The reason I wanted to use a cursor apart from not being able to see a set solution was so that I could call an sp I had already written that did a lot of the work for me.
Many thanks for all the words of wisdom.
I'll set when I can, and cursor when I can't.
-
Agree with Andy Warren.
TSQL can be a mini program in itself, but one needs to have the complete picture before starting to code.
Too much of a speed freak to use cursors except in severe cases.
-
As a purist, I tend to avoid cursors. But, there are times when I find a cursor the better option.
Perhaps the biggest reason I will use a cursor is for concurrency issues. If I have a major update,
I don't want to lock up thousands of rows, or have the whole table locked up. A cursor solves that
problem (yes - there are set based approaches to this).
As to the comment that it is part of ANSI SQL, yes, but it was not part of the original relational database design.
My memory is a bit fuzzy, but I recall that Oracle pushed to have cursors added. Oracle was an early
relational database design, and it used the older ISAM (indexed sequential access method) approach used by
those old COBOL apps - that's why Oracle uses cursor's for practically everything!
AFPeterson
What's the business problem you're trying to solve? -
Remember the 90-10 rule. 90% of the code runs only 10% of the time, and 10% of the code runs 90% of the time.
Why sacrifice readability for a piece of code that rarely runs and doesn't need the extra speed? Of course, I'm a big fan of well optimised code but you do need to weigh up the costs of a maintenance programmer's sanity to the performance increase.
-
quote:
Remember the 90-10 rule. 90% of the code runs only 10% of the time, and 10% of the code runs 90% of the time.Why sacrifice readability for a piece of code that rarely runs and doesn't need the extra speed? Of course, I'm a big fan of well optimised code but you do need to weigh up the costs of a maintenance programmer's sanity to the performance increase.
I do agree overall. However, I would have to add it is a good exercise having them rewrite optimally an not let them become lazy in their coding habbits even if they may feel overwhelmed a bit. Not enforcing good quality coding is the major reason so many companies suffer various issues from quality to performance and a programmers sanity in many cases is their (in)ability to accomplish the actual work. Force them to get better and not just say it is not very important or things will only get worse as they half-@$$ things when they know they can get away with it. This is like saying the code is self documenting. Just my humble opinion.
-
I just have to weigh in with the 'purists' on this one.
I won't argue that cursors are an appropriate or inappropriate solution. I agree with all the preceding that they can indeed be the best means of accomplishing the task at hand. Generally, I find they aren't; and a set-based solution exists. I guess I'm kind of a freak in that I find the set-based stuff easier to read and maintain than cursor code. I also acknowledge that not using a cursor can lead to some seriously acrobatic T-SQL... but I don't see how that's different from any other development environment's trade-offs. Sometimes the 'best solution' <ducking> (if such a thing exists) is going to be acrobatic.
Documentation, documentation, documentation.
SJTerrill
-
To me, the rule is to avoid cursors, but not at the risk of jumping threw hoops to make your code run.
There have been times I've loaded the data I'm cursoring on into a Temp table to solve locking problems for complex reports, etc.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply