April 14, 2009 at 8:25 am
I thoroughly enjoyed the cleverly-written, humorous introduction, and I look forward to the rest of the series.
I have found that SQL Server 2005 was a big leap forward towards eliminating the need for cursors. Now I'll need some convincing that they are no longer needed at all, but I'm ready to be convinced 🙂
My favorite thing about cursors is when I come into an organization where my predecessor used a lot of them. Then I get to be a hero by rewriting the worst of the old code and getting orders of magnitude performance improvement.
In fact, the title of this series inspired me to write new lyrics for the first verse of the great Paul Simon song - Barry, this is for you:
My predecesor was a genius, that is true
But unfortunately SQL wasn't what he liked to do
So I'm cursed with code that's waterlogged with cursors through and through, but there's
15 ways to lose your cursors
There must be,
15 ways to lose your cursors.
April 14, 2009 at 8:26 am
There are several of us who would take on the challenge of attempting to rewrite a cursor based solution as a set based solution. If you take up the SSC community challenge, be sure to provide complete information on what needs to be done, including table definitions, sample data, and expected results based on the sample data. Be sure any code you post is properly formatted, and data readily consumable.
April 14, 2009 at 8:27 am
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?
April 14, 2009 at 8:31 am
Thanks sushila for your comments so I don't have to. I agree with what you said.
I enjoyed the article and am looking forward to seeing the set based solutions to come out of it.
I occasionally still use cursors for temporary solutions where performance is not an issue and I need a quick solution and don't have time to think about it (or don't want to). But for production environments I certainly try to avoid them.
My recent experience has been to help resolve performance issues with a system. What did I find? Procedures utilizing cursors...and not just one cursor, but one or two embedded cursors and thirty pages of code. After spending a couple weeks figuring out what each procedure was doing and rewriting it using set based code, some new in SQL 2005, but most available in SQL 2000, I was able to take the execution time down from 3.5 hours to 1.5 minutes and from 1.5 hours to 8 minutes...slight improvements in performance. Only a hundred or so procedures to go... Along the way I learned a little history. These procedures were written by new programmers that new little about T-SQL and they were written when the databases had only a few thousand records. Now some databases have several million records and many more databases on a server so now we're feeling the results of this type of programming.
So part of the culture I'm trying to instill is for our database developers to get away from cursors and write set-based code. I'm hoping this series will help with this goal. Thanks for the article!
April 14, 2009 at 8:44 am
Thanks for the great article.
I also am waiting for the rest of this series. Hopefully, the wait is just daily.
I have a new job. With a simple
select object_name(object_id), * from sys.sql_modules where definition like '%cursor%'
I see that the main database has 77 procedures (including some triggers) that use cursors. I'm eagerly looking for the rest of this article to help me drastically reduce, if not completely eliminate, the number of these.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 14, 2009 at 8:44 am
This code is considerably faster:
Declare @X int
Set @X = 0
Select @X = @X + 1
From master.sys.columns c1
Cross Join master.sys.columns c2
Print @X
April 14, 2009 at 8:54 am
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.
April 14, 2009 at 8:56 am
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
I put forth a real world problem to that person that I could find no way to accomplish without using a cursor. I never heard back from themI don't suppose you remember it do you? I'd love to have a crack at it if you do 🙂
In a nutshell, it was a sliding fee calculation that was based on user selected criteria with capitations involved. The process had to return a customer's fee based on the user entered criteria. For example: the fee might be based on Income from a user's custom table and number of dependents from an HR application table, but the fee might also have a capitation, for example, for the first $500 use this fee if it meets all the criteria, for $501 to $1000 use this fee etc, plus it could also be date range driven and also based on the type of service provided. So an example might be:
Customer makes $20,000 stored in user.table.income and has 3 dependents stored in application.demographic.dependents. Sliding fees might be $0 to $1000 income with 0 dependents $30 fee,
0 - $1000 with 1 or 2 dependents $20 fee, 0 - $1000 with 3 or more dependents $10 fee. $1001 to $5000 with 0 dependents etc etc. with this set of fees valid for services a, b, and c. up to a maximum of $500 or 30 visits. Then you have the next set of sliding fees for services d and e with different ranges and $ values and valid for January thru June. Etc etc etc. Note that the criteria for comparing against any particular sliding fee configuration could change from one to the next. One fee might be based on income and dependents. The next might be income alone, the next by be dependents alone, the next might be some completely other criteria. Believe it or not I have simplified the actual requirements here just so I could fit it in a relatively short post.
I got a headache trying to figure out how to do this "set based". I ended up using a cursor to build dynamic queries. Executing the dynamic query and if I got a result I had my fee, if not, onto the next cursor row.
April 14, 2009 at 9:00 am
Since cursors are so "evil", I would like to see someone write sql that will send emails to a table of users. Each user should receive a different email containing only data that they are responsible for updating in the database.
Having said that, this is nearly the only thing that we use cursors for, sending email to users in the field, where each user receives a different email because they are responsible for a different subset of the data in the database. I've not yet found a way to do this without using cursors or while loops.
April 14, 2009 at 9:01 am
OK, the issue is simply one of coding style for the most part. The optimizer should handle "declarative" code [whatever that is] in much the same way that it would handle cursors or the product [database] is not built right.
I write cursors all the time without any appreciable difference from other code. It's all basic procedural programming. There is no declarative and cursor-driven difference.
April 14, 2009 at 9:02 am
neufeldb4 (4/13/2009)
Allow me to make some observations.1: This article was insulting. I am an intelligent professional with many years of experience. I design my code thoughtfully and carefully and my projects are successful because of it. I read articles that challenge me to change my practices because I am always willing to learn and improve. This article was not in that category.
I am sorry to hear that. This series of articles is intended first and foremost to challenge professionals assumptions about these issues and it is intentionally neither compromising nor conciliatory for that very reason. I am sorry if you find that kind of challenge or my manner of presenting it insulting.
2: This article was only nominally about cursors but really had little to do with them. From rambling analogies to amateur psychological observations, and insults, this was more of an exercise in creative writing than something designed to aid a person who is seriously interested in optimizing their code. If you wish to be of assistance to anyone, get to the heart of the matter. We are database programmers, not marketers. It is perfectly ok to use big words and get technical.
Clearly we disagree about what the heart of the matter is. There have been many attempts at direct technical-only articles on this subject in the past. IMHO, they have not had nearly the persuasive success that they deserve for two reasons. First because they were not comprehensive in their coverage of cases, and many readers respond that these cases did not apply to them or their specific cases. I am attempting to address this in my series, but there are a lot of cases and it will take time. And yes, I am starting with the simpler cases first and moving to the progressively more complex.
Secondly, although you may put much thought and consideration into each routine that you write, IMHO the majority of code is written in a much different way. In my experience, most code is written from the perspective of the first acceptable approach that the developer thinks of and most developers only occasionally try to research other ways of implementing a particular solution. In this situation Cursors and explicit loops become the universal last resort solution to every problem because developers can apply everything that they already know how to do from procedural programming (thus, the duct tape analogy). Most articles do not reach these developers because they do not address some of the real reasons for using Cursors: comfort, ease and familiarity.
This series attempts to remedy this, not by having a superior technical demonstration (there are plenty of those) but rather by being both approachable and comprehensive in its application. I realize that this will be beneath some people, however, those other approaches have been tried and you have rejected them as well.
As for the shot about "amateur psychological observations", all human enterprises are built on our ability to understand and relate to others perceptions, rationale and thought processes, including our professional relations. Similarly, it also deeply affects our ability to interact with each other successfully. Psychologists may be the professional students of this, but we all engage in it and our expertise at it is part of our professional expertise. Likewise, our observations and conclusions drawn from it are also part of our professional knowledge and skill set.
3: If you are going to use an example, and you wish to have credibility, make your example a good one. Take a close look at the straw cursor example. Even though it has only a few lines in it, it has two significant technical problems. The first is that it does nothing (I can save you even more precious processing power with the delete key and wipe out that code entirely). The second is that, even for this useless example, the cursor is built wrong. Take a close look at it and see if you can determine what is wrong with it. I'll give you a hint, it is not a syntactical problem.
First this is an adaptation of several real examples, not a straw man drawn from the ether. Secondly, as other posters have pointed out it does actually do something, so you should test it again. As I noted in the article, figuring out what it does is part of the problem. Finally, I have made every attempt to insure that the examples, especially the Cursor examples, are written as well as they can be. If you, or anybody else, thinks that any of my examples could be written better, please post your improved versions.
This is the type of problem I have seen in 100% of the articles like yours that claim the cursor is a thing to be scorned from the archaic past - none of them show a properly built cursor. This definitely impacts my perception as to the credibility and ability of the author. If you make mistakes that I do not tolerate in junior programmers, then how am I to take anything you say subsequently as being worth serious consideration? I do make use of cursors for some of my processes and, I can assure you, that the instances where I use them, one would not be able to improve either performance or readability with their removal.
Well, you have me there. My expertise is in re-writing Cursor routines, not in writing them in the first place. If you could please demonstrate for us an example of this as a "properly built cursor" then I will endeavor to use such in my examples and comparisons. It is neither my intent nor my desire to use inferior versions of the Cursor examples to make my points.
[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]
April 14, 2009 at 9:06 am
Jorge Vinuales (4/14/2009)
Hi, and sorry for my englishOk. I think I'm going to enjoy with the article series. I am an enthusiast of SQL-Set (declarative) programming, but when will arrive the meat?. Too much introduction. I'm hungry. ...
Thanks, Jorge. The next installment is currently scheduled for April 27th.
[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]
April 14, 2009 at 9:09 am
That is the point I was making. The author assumed that by changing his code from the individual steps 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.
April 14, 2009 at 9:10 am
I thoroughly enjoyed reading this. I do use cursors when I get stuck and I'm anxious to see if I am able to pick up any new ticks.
April 14, 2009 at 9:11 am
bruce.trimpop (4/14/2009)
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
I put forth a real world problem to that person that I could find no way to accomplish without using a cursor. I never heard back from themI don't suppose you remember it do you? I'd love to have a crack at it if you do 🙂
In a nutshell, it was a sliding fee calculation that was based on user selected criteria with capitations involved. The process had to return a customer's fee based on the user entered criteria. For example: the fee might be based on Income from a user's custom table and number of dependents from an HR application table, but the fee might also have a capitation, for example, for the first $500 use this fee if it meets all the criteria, for $501 to $1000 use this fee etc, plus it could also be date range driven and also based on the type of service provided. So an example might be:
Customer makes $20,000 stored in user.table.income and has 3 dependents stored in application.demographic.dependents. Sliding fees might be $0 to $1000 income with 0 dependents $30 fee,
0 - $1000 with 1 or 2 dependents $20 fee, 0 - $1000 with 3 or more dependents $10 fee. $1001 to $5000 with 0 dependents etc etc. with this set of fees valid for services a, b, and c. up to a maximum of $500 or 30 visits. Then you have the next set of sliding fees for services d and e with different ranges and $ values and valid for January thru June. Etc etc etc. Note that the criteria for comparing against any particular sliding fee configuration could change from one to the next. One fee might be based on income and dependents. The next might be income alone, the next by be dependents alone, the next might be some completely other criteria. Believe it or not I have simplified the actual requirements here just so I could fit it in a relatively short post.
I got a headache trying to figure out how to do this "set based". I ended up using a cursor to build dynamic queries. Executing the dynamic query and if I got a result I had my fee, if not, onto the next cursor row.
If you have the DDL for the tables, sample data (in a readily consumable format), and expected results based on the sample data that would be great.
Viewing 15 posts - 31 through 45 (of 380 total)
You must be logged in to reply to this topic. Login to reply