There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction
-
Lynn Pettis (4/14/2009)
dbishop (4/14/2009)
I must say that for an introduction you did an okay job. The only thing I took offense of was the procedural vs. declarative examples and your definition of procedural languages (VB, C#, Java). I've alsways heard these referred to as event-driven, not procedural.COBOL and Fortran are procedural languages.
Encapsulating the steps of making cookies into a Cookies 'class' with a property to add chocolate chips is a very poor example. Because making cookies is a procedure. You cannot bake the cookies until you add the flour, baking powder and salt. And you cannot do that until you cream the ingredients, and you cannot do that until you get the butter and sugar. All you've done by saying Cookies is 'hidden' the procedure. And what if you are making no-bake cookies. Or cookies for someone on a salt-free diet?
Yes, VB.NET, C#, Java are event-driven languages; however, if you look at the code behind each event you will find procedural code.
That is the point I was making. The author assumed that by changing his code from the individual steps (procedures) to Cookies, with chocolate chips, all of a sudden it was no longer procedural. I was trying to point out the falisy of that statement.
-
Manie Verster (4/14/2009)
Hi Brad,I think that if you have a better way of doing the cursor then you should post it so we can all see how to do it right. Barry's cursor is the only way I know how to do a cursor and I would like to see a better way of doing it.
Barry, don't degrade something that was put there for a purpose. I agree that cursors and loops are not the best way of doing a query but sometimes you have to loop in order to do something. Example: I have a client that dowloads their bankstatements from the internet and then upload it into our system. We then give them the opportunity to auto-allocate their bankstatement items to various accounts. This means the the stored proc that does that have to loop through every record and basically strip the text in a column to pieces to see where this needs to be allocated to. I done see that done in a set based procedure. Cursors might not be your best option but in some case they are the only way to go.
Anyway, thanks for your article and I am looking forward to the rest.
Thanks for your thoughtful comments Mannie. As for Cursor's "purpose", one of the central points of this series is that those original purposes largely no longer exist because the product has been improved to the point where where there is no longer a need for Cursors.
As for your example, obviously it's impossible for me to comment without the specific details. If you code post it here or PM it to me I would be happy to let you know what I think.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
dm_unseen (4/14/2009)
I would say to all posters that complained about the lack of substance:Take a Hike, Ike 😀
I like the reference, but then I'm also a fan of Paul Simon.
I would suggest to wait for the follow-up posts, because the current post is obviously a small (narrative) introduction to an intersting problem with a lot of interesting set based solutions;-)
Thanks for the support dm_unseen. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
dbishop (4/14/2009)
Lynn Pettis (4/14/2009)
dbishop (4/14/2009)
I must say that for an introduction you did an okay job. The only thing I took offense of was the procedural vs. declarative examples and your definition of procedural languages (VB, C#, Java). I've alsways heard these referred to as event-driven, not procedural.COBOL and Fortran are procedural languages.
Encapsulating the steps of making cookies into a Cookies 'class' with a property to add chocolate chips is a very poor example. Because making cookies is a procedure. You cannot bake the cookies until you add the flour, baking powder and salt. And you cannot do that until you cream the ingredients, and you cannot do that until you get the butter and sugar. All you've done by saying Cookies is 'hidden' the procedure. And what if you are making no-bake cookies. Or cookies for someone on a salt-free diet?
Yes, VB.NET, C#, Java are event-driven languages; however, if you look at the code behind each event you will find procedural code.
That is the point I was making. The author assumed that by changing his code from the individual steps (procedures) to Cookies, with chocolate chips, all of a sudden it was no longer procedural. I was trying to point out the falisy of that statement.
Actually, I think you are taking it somewhat out of context here. In C#, for example, you still have to write the procedural code to support making the cookies. In SQL (MS SQL Server, Oracle, Sybase, InterBase, Firebird, MySQL, PostgreSQL, etc) you write what you want and the database engine does the rest without you have to write the necessary code. For example, SELECT CustId, CustLastName, CustFirstName FROM Customers WHERE State = 'CA', not one bit of procedural code need be written.
-
Matt Whitfield (4/14/2009)
GabyI have to admit - I wasn't too kind on your original article. BUT - it did spawn what was possibly the most interesting thread ever on SSC. You should be proud! 😀
It's my legacy...*sniff* 🙂
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein -
Wedge78 (4/14/2009)
Hi AllI agree that you can run into problems when using cursors without thought
However I have an instance where I have to gather information from a 3.2 billion row partioned table. The table is partioned on website_id
and table houses keyword data by page.
I have to pull information out of this table for potentially thousands of websites and if I do it set-based it takes a large amount of time
I changed to use a cursor as the speed of the retrieval of this information was cut by 50 times when using a cursor which was a massive saving and allowed people to use the report it was intended for
I welcome all examples on this subject. If you could post your Cursor routine and the previous set-based example along with the table definition, then I would be happy to take a look at it.
So I gues what I am saying is that its all good and fine to bash cursors but would it not be fair to conceed that there is a place for cursors?
I'll need an example to convince me of that.
And if you are adament that cursors should never be used I will be happy to use my example as a proper production test case to be proved wrong
Thanks for that offer, Chris, and yes, that is exactly what I would like.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
gserdijn (4/14/2009)
Nice introduction. I am sure no offence was intended, and non taken by me.The example was trivial, but let's wait and see what comes next.
Thanks, gserdijn!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
neufeldb4 (4/13/2009)
Allow me to make some observations....
3. ... The first is that it does nothing
The code returns the square of the number of rows in master.sys.columns, trivial I know but I wouldn't call it "nothing". The obvious set based re-write is just
Select sum(1) From master.sys.columns c1 Cross Join master.sys.columns c2or more usually
Select count(*) From master.sys.columns c1 Cross Join master.sys.columns c2neufeldb4 (4/13/2009)
Allow me to make some observations....
3: ...the cursor is built wrong.
what do you mean by "wrong"? Can you post a "correct" version.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
kevriley (4/14/2009)
Come on guys (and gals) - this is obviously a multi-part series, and this part was subtitled 'Part 1: An introduction'....
Once the series is complete, if you still can't replace your cursors with something that runs faster without them, post it here. There's plenty of people who will gladly help - and no - they wont berate you for using them in the first place! They just be happy that they helped make your code run faster, and helped you learn some ways of avoiding cursors.
Thanks for the confidence Kev, I'll do my best to deliver on it.
And lest anyone get's the wrong impression: Although I have never favored them, I have used Cursors in the past myself, however, since the release of SQL Server 2005 I have been slowly persuaded over the last few years that the number of acceptable use-cases for cursors has dropped from a small percentage to virtually nil. But it took a lot of instances before I became convinced. So this is not intended to deride anybody for having used Cursors. Rather, it is intended to encourage a fresh look and reevaluation of their use.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Matt Whitfield (4/14/2009)
Looking forward to part 2, sir. 😀If you want an extra sounding board, let me know.
Thanks, Matt. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Interesting article. Would be waiting for second installment. Cursors are bad ( period ) but in some situations we had to use them. It becomes necessary.
The example typically says why not use cursors. it took 4:49 minutes to execute while select count(1) From master..syscolumns c1 Cross Join master..syscolumns c2 takes 1 second.
Execution plan interestingly shows
Query 1: Query cost (relative to the batch): 0.00%
SQL DBA.
-
Here are some points that are good about cursors or while loops I feel compelled to share. I've been working with SQL Server since 6.0 amongst other ware. I currently work for an ASP that hosts multiple applications running on SQL Server 2005 (64bit) to over 3,000 companies.
1. If you need to provide a business need such as a report, or a batch process, and the implementation of the database does not serve up the data nice and neetly and its not practicle to change the design and creating temporary tables and/or inline sets or cte's ALONE prior to final resultset cannot get you there, then cursor(s) or while loop(s) may be an option. This again, depends.
2. If you already have complicated logic contained in stored procedures used by parts of an existing application and you need to use them, cursor or possibly a while loop may be best. And again, this also depends. If you have a billion records to update, you may be reconstructing the code to avoid the cursor.
3. If you are in a situation where you need to use dynamic sql and it requires the use of iteration when constructing it then a cursor or while loop works fine under certain circumstances. I could also get into an argument here about dynamic sql, i.e., its pros and cons. And yes, dynamic sql and cursors both have served well when needed.
4. And to try and end this properly, if your sql is involved in the running of business transactions, i.e., sales orders, gift cards, loyalty cards, credit cards for custumers or any transactions where the occurances are high in volume or you want your application to scale to high volume, don't use cursors and don't use dynamic sql ever as part of these transaction. I will go even futher to say don't even use temporary tables as part of these transactions. Design or redesign your database implementation to be optimized as much as possible.
-
Peter Hansen (4/14/2009)
I've read a number of "get rid of cursors" articles. None of them have addressed the issues that I have faced (and solved) with cursors. Perhaps this series will be different, I would certainly love to pick up some new ways to approach the sort of complex problems that appear in the real world.I recently completed a very complex bit of code that has three nested cursors. The horror! The fact is that it works well because I have limited the number of rows (the outer cursor processes a few hundred rows, the inner cursors a handful). The specific nature of the problem I solved had to do with complex business logic that needed to be applied to each row, with a series of cascading rules. One other major issue I have is that I believe that code needs to be easy to understand. One issue I have with set-based coding is that it tends to obfuscate the logic when things get to be complex. In some cases, cursors have a readability advantage.
I come from a procedural background, that certainly colors my view of the world. But I am wary of those who proclaim that any given technique is universally bad. Almost 40 years or writing code in various forms has taught me that there are mutiple approaches to any given problem, each with trade-offs. The real skill of programming is to pick the best approach. At times, cursors are that best approach.
It must be mentioned that my environment is Sybase (and an old version at that!), not SQL Server. Living in this relatively primitive environment I can't take advantage of the latest features. We often have to make do with what we have.
Peter Hansen
Peter:
I certainly do want to address every different set of reasons that developers see for using Cursors. If you could give an example of the reasons that you see and/or an example(s) that can demonstrate it, I would love to take a look at it.
Even though I come from a procedural background as well (I have written at least 3 times as much procedureal code as declarative) I still find that the readability of declarative code is better a large majority of the time. I would be interested in any counter-examples of this, if you have them.
As for the Sysbase thing, yes, much of my premise rests on the capabilities added with SQL Server 2005 and even in SQL Server 2000. And I have not worked on Sybase in 15 years, so I do not know how much of this might apply there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
noxidjkram (4/14/2009)
Another vote for a well written introduction.For all those experienced and insulted DBAs out there, please remember the up-and-comings like myself who really appreciate an introduction to the subject wherever possible.
I, like many others I suspect, are expected to use SQL as part of their job but have never been offered/given training - i've learned what I know from a DBA and a Systems Analyst that I work with over a period of nearly 2 years now, and I know my knowledge is shockingly bad (i have no theoretical knowledge at all).
In summary - Thanks for the article, and I'm looking forward to reading part 2...
Thanks noxidjkram! (hmm, not sure I could pronounce that if I had to... 🙂 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Anirban Paul (4/14/2009)
Very nice article and really well explained. I will also enjoy this series....Thanks, Paul. That means a lot coming from you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 46 through 60 (of 380 total)
You must be logged in to reply to this topic. Login to reply