April 16, 2009 at 4:23 am
Jeff Moden (4/15/2009)
Thomas (4/15/2009)
However, I have, in my travels, run into situations where even though a set-based solution existed, it performed worse than a cursor in that particular version of the DBMS for that particular problem. I suppose it is akin to denormalizing. You have to know the reasons for normalizing and be versed in its use before you can consciously decide to deviate for a particular solution.Absolutely correct... except that most of those "set-based" solutions that perform worse than a cursor aren't actually set-based. They just look like it. Just because it has no While Loop or explicit RBAR, doesn't mean it's set based. Those poor performing solutions are giving "good" set-based code a very bad reputation. One of the more common and "deadly" reasons for such poor performance can be found in the following article...
Don't forget the 'i've written a scalar function that does a whole load of lookups and i'm using that function on a large data set instead of doing an inner join' kind of hidden RBAR 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 16, 2009 at 7:05 am
Jeff Moden (4/16/2009)
...For those who don't know what a "pseudo-cursor" is, here's one of the simplest answers I can give...
SELECT columnlist
FROM sometable
Think about what that little slice of computational heaven does behind the scenes and you'll understand why the T-SQL Ninja's on this site call it a "pseudo-cursor". And, sorry if it's not intuitive, but it should be. 😛
Damn... I hope I'm not stealing any of Barry's thunder for part II.
Hmm, while that is beautiful in its simplicity, I can't help think that something is missing... 🙂
And don't worry about stealing my thunder, pseudocursors are way at the end of the series.
[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 16, 2009 at 7:06 am
Forgive me if I missed anything by skipping to the end discussion, as after 18 pages of reading I believe I got the theme. It is Time to add my own take on the article.
my take on the format of the article
For me the introduction was too longwinded and in the end not really to the point. It is biased towards your opinion on matters. It is fair enough for you to highlight your take on things, but I liked to have seen the buildup much shorter as it would have made the article more pleasant to read without weakening your argument.
It felt like saying bad/stupid 25 times in sequence and louder and louder using slightly altered words to make your point gain additional value, which of course does not work as can be witnessed by the offense some people took. These are also people you want to reach and get constructive feedback on content from, a very much missed opportunity.
my take on the content
While I do understand this is a part one, I don't liked seeing this part stripped to the bone in that essentially there is no true content to discus. What remained is a very contrived and practically value poor…no…value-less example. I remember an earlier discussion you and I were both in (about pseudo cursors, which come back in a bit no doubt). In that discussion you also had a contrived and very narrow example involving simply counting. It did not sit well with me back then and I have the same issue with your example here.
Your example has all the hallmarks of needing a bad example, so let’s do something utterly insane and work from there to make your already established personal points. Granted you will find many real world situations that are bad and involve cursors and loops, but surely not as bad as the one you provided. My core arguments here is that your example just counts, something that no one serious and at any level of SQL would try to solve by using a cursor. In fact they would need additional SQL knowledge just to use a cursor method to count, making your arguments essentially pointless.
Also your opinion is that procedural code is pretty much always less understandable then functional code. This is besides depending on personal experience and knowledge, not by inherently true. Many of the truly fast SQL set based code that replaces procedural code are anything but transparent and easy to understand. In such situations it becomes quite important to abstract the set based building blocks into set compatible inline functions.
If you do not break down a complex solution into functional parts then any set based, one query solution demands that you fully understand every aspect of both the problem and SQL code implementation. This can apply this logic to your solution or one that someone made two years back on another project you never worked on before.
You on the other hand declare set based code where you need to understand the whole and all the details fully beforehand as proof of it being a better way to code. To me on the other hand it signifies a handicap that in practice it is actually bad. I think it is better to reduce complex problems to several simpler ones and solve those independently. By reducing problems to a level people can comfortably work with you not only reduce the margin of error but also add flexibility in that function/implementation details can later be modified without having to re-understand / redo the whole in full all over again.
So while “set based” is function over implementation, in practice the reverse quickly becomes true as complexity increases. Is this an argument of me against set based solutions....NO! It is just an argument from me that your arguments are not strong enough to stand on their own, especially in the light of a contrived example to make a point.
I really wished you had put in a few examples in that actually do something that people can relate to. Not necessarily solutions, but simply code that you seen in the field that tackles a problem using cursors and other solutions to a problem that is better solved without. And build on this a case that naturally results into your arguments. Thus arguments resulting from facts instead of facts being sought or contrived in order to support pre-established arguments as is the case in this article.
As it stands now, your article is in my opinion nothing more as a rant without any substance to back it up. I know you are quite capable and you should be able to do a much better job at making a case for set based solutions over cursors. You make some bold claims here and the reader is to accept them as true with nothing to back them up. I seriously hope you make up for this it in the subsequent parts of the series as otherwise you won’t be doing people a favor.
Because of our previous rubbing in another thread, I want to make sure you understand my hard criticism is not meant as a personal attack. I tried very hard to be as exact as possible and to the fact and I hope you read it as such. If this fails I simply withdraw from this series and conclude we really cannot understand each other (which would be a shame).
April 16, 2009 at 7:07 am
Jeff Moden (4/16/2009)
Mr. Young... very nice job. Immensely entertaining article and follow up posts to boot. My hat is off to you, Sir... I know just what you're going through trying to keep up with all the posts on this wonderful thread. Thank you for taking the time to write such a great article. I can hardly wait for part two.
Thanks, Jeff. Praise from the master, himself. 🙂
[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 16, 2009 at 8:06 am
peter (4/16/2009)
...my take on the format of the article
For me the introduction was too longwinded and in the end not really to the point.
Then I am not sure that you got the points (plural) that it was trying to make: that the issue here is about a lot more than just how fast does something run or how long did it take me to write it. It is also about the habit of using an inappropriate solution just because we are familiar with it and also the widespread belief that we need Cursors and cannot live without them.
It is biased towards your opinion on matters.
I believe that presenting things from the author's perspective is both typical and appropiate for a narrative metaphor.
While I do understand this is a part one, I don't liked seeing this part stripped to the bone in that essentially there is no true content to discus. What remained is a very contrived and practically value poor…no…value-less example. I remember an earlier discussion you and I were both in (about pseudo cursors, which come back in a bit no doubt). In that discussion you also had a contrived and very narrow example involving simply counting. It did not sit well with me back then and I have the same issue with your example here.
Ah, you're that Peter. As I recall I was responding to your unsubstantiated claim that the narrow example of the string concatenation example proved that FOR XML would always be faster than pseudocursors because pseudocursors executed statements in a loop. Of course there isn't anything correct about that claim and you seemed to take real personal umbrage at my pointing it out. Sorry you feel that way.
Granted you will find many real world situations that are bad and involve cursors and loops, but surely not as bad as the one you provided.
It is a converted real example that I have seen many times. In fact there are multiple instances on this very site.
My core arguments here is that your example just counts, something that no one serious and at any level of SQL would try to solve by using a cursor. In fact they would need additional SQL knowledge just to use a cursor method to count, making your arguments essentially pointless.
A well-crafted argument that is completely confounded by the actual facts. SQL routines like this are, in fact, being written all the time. Whether you consider these people "serious at any level of SQL" is irrelevant. They exist, they write SQL and some of them do frequent this forum and even read the articles. This series is first and foremost for them.
And I agree, they actually had to learn more to write that Cursor than they would have had to to learn to do it correctly (I think that I mke this point in the next installment). Nonetheless, the pull of procedural programming is incredibly powerful on those who already know how to do it.
Also your opinion is that procedural code is pretty much always less understandable then functional code. This is besides depending on personal experience and knowledge, not by inherently true.
I intend to demonstrate this as the series goes on. And for the record, my personal experience and knowledge is overwhelmingly in the realm of procedural programming, by a factor of at least three to one, so I do think that I can be a fair judge of this. I welcome counter-examples on this matter.
I really wished you had put in a few examples in that actually do something that people can relate to. Not necessarily solutions, but simply code that you seen in the field that tackles a problem using cursors and other solutions to a problem that is better solved without.
Patience, there is a lot of ground to cover on this subject and it will take time. I am starting with the simplest cases and progressing to the more challenging onse as we go. But I assure you that every single cursor example is taken from actual real-world cases.
As it stands now, your article is in my opinion nothing more as a rant without any substance to back it up. I know you are quite capable and you should be able to do a much better job at making a case for set based solutions over cursors. You make some bold claims here and the reader is to accept them as true with nothing to back them up.
Hmm, well I am sure that Steve would be happy to receive articles from you on this subject as well.
Because of our previous rubbing in another thread, I want to make sure you understand my hard the criticism is not meant as a personal attack. I tried very hard to be as exact as possible and to the fact and I hope you read it as such.
Uhh, OK, if you say so.
[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 16, 2009 at 8:33 am
I have been a programmer for over 20 years. We all know that cursors are bad. We were all told that when we learned how to do it. It's like using a goto statement in programming. (sometimes it is needed)
I for one look forward to seeing how I can learn something new, How to get the job done without cursors. And sometime it might be a better solution? Tell me more!
My only complaint...When do I get to learn more?
April 16, 2009 at 8:38 am
mtrafzer (4/16/2009)
My only complaint...When do I get to learn more?
The next installment is scheduled for the 27th, though Steve might move that up.
Also, there are many, many other opportunities to learn on this site. 🙂
[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 16, 2009 at 8:50 am
RBarryYoung (4/16/2009)
mtrafzer (4/16/2009)
My only complaint...When do I get to learn more?The next installment is scheduled for the 27th, though Steve might move that up.
Also, there are many, many other opportunities to learn on this site. 🙂
Sir,
Since I subscribed to this website, my t-sql quotient has risen 100%++. Specially with SQL 2005. The truth now is, that I can make a new SQL project better because of the education that I have obtained by reading threads like this one, reading between the lines from people like (RBarryYoung , Lynn and many more contributors) and really great articles on this site.
So I want to thank all of you (you know who you are) now if I haven't' done so before.
AL
April 16, 2009 at 9:06 am
RBarryYoung,
First thanks for your quick reply. It seems that last time you understood me wrong and I did not pick up on that. And from there the discussion wend downhill. I probably worded it wrong causing you to pick on something that was not really what I tried to say. The way you present it here makes perfect sense, but was not my intended argument back then and you still misread me on what I did took personal there.
On the rest of your feedback, I think it is worded much better and explicit then in the original article. Personally I would not try to educate people by showing them really weird constructs that look like they must be made up for show. The counting example is such an extreme that I would assume the writer is not daily working with SQL in the first place and thus see it as an anomaly not worth correcting here. It certainly is not something I expect to see in an article about to prove to us why cursors should not be used and that there are *always* (as you stated it) better alternatives in 2005.
Please realize that that a converted real example can totally take away the reason as to why the code was written as is. If the original code was about counting records as it looks after your conversion, you have a valid case. But if not, then claiming it is real “out there code” is not valid.
Again I hope you make a good case in the follow-up articles. And yes, I will find some time to make an article about functions at some point. Like you most of my experience is outside the SQL and mostly procedural in nature. My take is that a lot of hot/fast SQL code is obscured by lack of abstraction and this forces a reader to understand the whole in detail. Which is not always possible in complex constructs, a human can handle only so much.
Either way, I don’t think procedural thinking is the demon you make it, but rather the lack of SQL knowledge that push people to inefficient procedural solutions. In any realm you got good and bad practices and in SQL Server, procedural code and RBAR is clearly bad if not extremely carefully applied. At the very least it is severely penalized by SQL Server as is. If your article improves people’s knowledge and provides good workable solutions I am all for it.
I just have a problem with the claims made without anything backing it up (yet). It makes it look like a rant to me while that cannot truely be your intention behind it. Of the 15 ways you could at least have included 2 or 3 😉
April 16, 2009 at 9:48 am
peter (4/16/2009)
First thanks for your quick reply. It seems that last time you understood me wrong and I did not pick up on that. And from there the discussion wend downhill. I probably worded it wrong causing you to pick on something that was not really what I tried to say. The way you present it here makes perfect sense, but was not my intended argument back then and you still misread me on what I did took personal there.
Fair enough, let's let bygones be bygones then.
The counting example is such an extreme that I would assume the writer is not daily working with SQL in the first place and thus see it as an anomaly not worth correcting here.
But how much SQL code is written by people who do not work in SQL daily? Quite a lot I am afraid. And in my experience, people start developing in SQL as part of another job any usually are not trained in it (initially anyway, in fact, technically, *I* have never received any formal training in it either, but then I do read a lot 🙂 ).
It certainly is not something I expect to see in an article about to prove to us why cursors should not be used and that there are *always* (as you stated it) better alternatives in 2005.
As I pointed out to one of my earlier critics, there have already been lots of excellent articles written that take an approach closer to your expectations, but there are still plenty of practitioners who have not been reached by them. I think that I know one of the reasons for this and I am taking a different approach for that reason.
Please realize that that a converted real example can totally take away the reason as to why the code was written as is. If the original code was about counting records as it looks after your conversion, you have a valid case. But if not, then claiming it is real “out there code” is not valid.
The only thing that I changed was the names, and yes, it ("they", actually) was trying to count the rows in a table, and in retrospect, the sys.Columns cross join trick may have been an unwise distraction, but I needed it to have enough rows to be relevant and also to be already present on all 2005 systems.
And hopefully you understand that there are really good reasons why I cannot just post the actual code and must rewrite them. Making sure that it works everywhere is one reason, but there are at least two others (confidentiality and ownership of the code).
Either way, I don’t think procedural thinking is the demon you make it, but rather the lack of SQL knowledge that push people to inefficient procedural solutions. In any realm you got good and bad practices and in SQL Server, procedural code and RBAR is clearly bad if not extremely carefully applied. At the very least it is severely penalized by SQL Server as is. If your article improves people’s knowledge and provides good workable solutions I am all for it.
I wouldn't say demonic so much as insidious. I have no problem with procedural solutions, in their appropiate realm, I just don't think that that includes the declarative realm of SQL.
I just have a problem with the claims made without anything backing it up (yet). It makes it look like a rant to me while that cannot truely be your intention behind it. Of the 15 ways you could at least have included 2 or 3 😉
The lack of technical meat in the first installment is a fair complaint. I may have misjudged that and I will strive to correct that in the future.
[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 16, 2009 at 9:55 am
Tnx, this is a great solution, didn't come to mind when I was writing the code (it was more classic "hurry up" approach 🙂 ), although I did wrote few cte's. I'll see if I can apply to the real code, there's much more messing about with data. Thanks once more.
April 16, 2009 at 10:04 am
Thomas (4/15/2009)
Using a cross join is not itself counter-intuitive. Using it in this fashion to generate a bunch of numbers probably is to most developers.
I think that this is one of the points that Jeff was making. Developers need to learn how to do set-based operations, so that when they see one it will be intuitive. I'm no master at this technique (yet, still working on it), but I've been working in SQL long enough, and have been trying to understand set-based work, that even I readily grasped exactly what was going on.
Yes, when you first see this, you have to think about it. Maybe more than once... 10, 20, whatever. But, once you do grasp it, and you practice it and work with it, when you do see it, you can immediately understand what is going on.
Jeff has completely immersed himself in the use of set-based operation in sql. As a consequence, he now thinks set-based. He sees set-based where others don't. Does this mean others should? Probably. Is it good to always think set-based? Well, lets just say I wouldn't want to have sex if it was done in a results-oriented, set-based method.
However, I have "seen the light". The problem is, I'm still learning it. I work at it daily. Maybe someday, I can teach Jeff something.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 16, 2009 at 10:12 am
mtrafzer (4/16/2009)
I have been a programmer for over 20 years. We all know that cursors are bad. We were all told that when we learned how to do it. It's like using a goto statement in programming. (sometimes it is needed)I for one look forward to seeing how I can learn something new, How to get the job done without cursors. And sometime it might be a better solution? Tell me more!
My only complaint...When do I get to learn more?
You're there... participate in some of the forums on this and other sites and you will learn more than you can possibly imagine.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2009 at 10:15 am
WayneS (4/16/2009)
Maybe someday, I can teach Jeff something.
You just did. Thanks, Wayne.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2009 at 10:19 am
dejan.kelemen (4/16/2009)
Tnx, this is a great solution, didn't come to mind when I was writing the code (it was more classic "hurry up" approach 🙂 ), although I did wrote few cte's. I'll see if I can apply to the real code, there's much more messing about with data. Thanks once more.
Thanks, dejan. Mind if I use your code as the basis for an example?
[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 - 211 through 225 (of 380 total)
You must be logged in to reply to this topic. Login to reply