Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction Expand / Collapse
Author
Message
Posted Monday, April 13, 2009 10:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 9,294, Visits: 9,484
Comments posted to this topic are about the item There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #696297
Posted Monday, April 13, 2009 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:07 AM
Points: 1, Visits: 12
Should use this code

Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2
Post #696299
Posted Monday, April 13, 2009 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:51 PM
Points: 1, Visits: 16
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
Post #696313
Posted Tuesday, April 14, 2009 2:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 PM
Points: 15, Visits: 260
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.


Post #696353
Posted Tuesday, April 14, 2009 2:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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.


Manie 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)
Post #696356
Posted Tuesday, April 14, 2009 2:29 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:17 AM
Points: 8, Visits: 285
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


Kind Regards,

DM Unseen AKA M. Evers
Post #696362
Posted Tuesday, April 14, 2009 3:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 17, 2010 7:42 AM
Points: 80, Visits: 167
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
Post #696373
Posted Tuesday, April 14, 2009 3:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:41 AM
Points: 1,255, Visits: 771
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.




Dutch Anti-RBAR League
Post #696380
Posted Tuesday, April 14, 2009 3:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 AM
Points: 2,716, Visits: 2,471
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
Post #696389
Posted Tuesday, April 14, 2009 3:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Looking forward to part 2, sir.

If you want an extra sounding board, let me know.


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #696390
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse