April 13, 2009 at 10:26 pm
Comments posted to this topic are about the item There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction
[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 13, 2009 at 10:30 pm
Should use this code 🙂
Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2
April 13, 2009 at 11:10 pm
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.
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.
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.
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.
Finally, If you can offer some advice as to how I can improve the quality of my work, I am only too happy to take it. If you insist on trivializing my work and insulting my intelligence, I will take note of that as well.
Brad Neufeld
Data Architect
April 14, 2009 at 2:02 am
Hi, and sorry for my english
Ok. 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.
By the way, I think this not should be a war between cursor and set-based developers stuff.
I would wait for the examples presented by author for debate and try to refute them.
Perhaps someone can show and example which can only be resolved by a cursor - based approach... or which has better performance in a cursor - based approach.
Let's enjoy of this interesting matter, don't make the war.
April 14, 2009 at 2:15 am
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.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 14, 2009 at 2:29 am
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;-)
DM Unseen AKA M. Evers
April 14, 2009 at 3:02 am
Hi All
I 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
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?
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
Chris
April 14, 2009 at 3:16 am
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.
April 14, 2009 at 3:42 am
Come on guys (and gals) - this is obviously a multi-part series, and this part was subtitled 'Part 1: An introduction'.
And at the end, the author states 'coming up in,part 2 ....'
So stop saying it is lacking in content! R Barry Young is just setting the scene for what I imagine is going to be a very informative discussion. And if you take offense at his analogy, I'm sure it is meant light-heartedly.
For all those of you who have a cursor based solution, and have tried to make it set-based only to discover that it runs slower, stay tuned to this discussion. You might learn a few tricks. Often an approach to replacing cursors with set-based solutions is flawed from the beginning as the mindset is still in row-by-row mode, and so you try to write SQL that maps onto your current solution, but is implemented using non-cursor TSQL. That's not a slur on your skills, it's just very difficult to break away from the 'answer' to a problem when you already have one that works!
Plus I guess some people here on the cursor side of the 'war' (not my description), will be naturally biased, and will be able to tells us all tales of woe where the attempt to replace the cursor with set-based has sent the query spiralling out of control. Ever stop to think you might have made an error?
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.
Kev
April 14, 2009 at 3:48 am
Looking forward to part 2, sir. 😀
If you want an extra sounding board, let me know.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 5:35 am
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
April 14, 2009 at 5:39 am
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
This sounds familiar! 🙂 We took that as a challenge last time, and we came up with a solution that was set based and increased performance. And we actually found some issues logically with the original implementation too.
Gotta love a challenge 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 5:43 am
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...
SQL Novice - Here to learn.
April 14, 2009 at 5:48 am
Very nice article and really well explained. I will also enjoy this series....
April 14, 2009 at 6:47 am
I think Brad took it personal!!! This article is an opinion and it is up to us to discuss and bring our comments in how to improve the a way to work with cursors. I believe that sometimes cursors might be useful, but after 30 years of programming in different languages and DB, there is always a better way to do it. For instance I was checking a process that was updating some records on a table, and noticed that the process was taken long time to execute, then I check on the code and found that there was a cursor but the problem was not in the cursor itself but in the way the cursor was used; it was updating the table record by record, so I took the result to a temp table and performed a massive update on the final table. What a surprise a process that it was taken more that 30 min. was reduced to 2-3 minutes and even less. In other hand, sometime programmers are assign to do certain taks and they tend to have "TEMPLATES" in their head and are not capable to think out of the box, that is why if you team lack of programming standards and code QA/QC and/or DBA team that approves the code the result is obvious. Remember we work base on FACTS and Barry have an opinion that we have to be intelligent enough (Do not take it personal) to contribute by providing a solution or giving you opinion that stands against the article. Finally I agreed with gautamsheth200 by doing a "Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2" you have the same result, but once again you have to analyze the situation and see if you are able to improve the code, by the way from 23 to 1 second by the solution presented.
Best regards to all
Vinicio Aizpurua
Systems Analyst/Developer/DBA
Viewing 15 posts - 1 through 15 (of 380 total)
You must be logged in to reply to this topic. Login to reply