There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by RBarryYoung » There Must Be 15 Ways To Lose Your Cursors......

385 posts, Page 1 of 39

1 2 3 4 5 »»»

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Rate Topic

Display Mode

Topic Options

Author

Message

RBarryYoung

Posted Monday, April 13, 2009 10:26 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

gautamsheth2000

Posted Monday, April 13, 2009 10:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 11:46 AM
Points: 1, Visits: 8

Should use this code Smile

Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2

Post #696299

neufeldb4

Posted Monday, April 13, 2009 11:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 1, Visits: 14

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

Jorge Vinuales

Posted Tuesday, April 14, 2009 2:02 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 12, Visits: 223

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

Manie Verster

Posted Tuesday, April 14, 2009 2:15 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:00 AM
Points: 1,151, Visits: 879

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.

Tongue

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today

Post #696356

DM Unseen

Posted Tuesday, April 14, 2009 2:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 17, 2013 1:28 AM
Points: 8, Visits: 284

I would say to all posters that complained about the lack of substance:
Take a Hike, Ike BigGrin

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 Wink

Kind Regards,

DM Unseen AKA M. Evers

Post #696362

Wedge78

Posted Tuesday, April 14, 2009 3:02 AM

SSC 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

gserdijn

Posted Tuesday, April 14, 2009 3:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 8:00 AM
Points: 1,065, Visits: 698

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

kevriley

Posted Tuesday, April 14, 2009 3:42 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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

Matt Whitfield

Posted Tuesday, April 14, 2009 3:48 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718

Looking forward to part 2, sir. BigGrin

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 »

385 posts, Page 1 of 39

1 2 3 4 5 »»»

Permissions