Cursors Be Gone!

Question

Cursors Be Gone!

Viewing 15 posts - 76 through 90 (of 272 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

npfitzner (1/1/2009)
Tom.Thomson (1/1/2009)
He wants to remove the From clause from the Update statement? Seriously?
No - I think that's what the 😉 was for.
🙂

Yes! He actually want's to delete FROM from UPDATE. The smiley face was an indication of something else this time...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 2

Jeff Moden (1/1/2009)
Yes! He actually want's to delete FROM from UPDATE. The smiley face was an indication of something else this time...

Daym.

Has anyone told him about using DELETE with JOINs? Hope not! 🙂

Happy New Year everyone, by the way!

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Matt Whitfield (1/1/2009)
Jeff Moden (1/1/2009)
Yes! He actually want's to delete FROM from UPDATE. The smiley face was an indication of something else this time...
Daym.
Has anyone told him about using DELETE with JOINs? Hope not! 🙂
Happy New Year everyone, by the way!

Have I missed something? Who is it that everyone one is on about?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

One of my fellow MVP's... Go back and look at Grant's posts... you'll figure it out. I've probably said too much, already.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lawrence-865109 Grasshopper Points: 24 More actions · Answer 5

Jeff, I've been following this post as I am a SQL Noob and walked into a position where cursors are everywhere and I'm trying to understand when/when not to apply them. I applied your speed trials and received proportionately similar speed times. The XML example was very fast...but I haven't seen that example brought up in other posts in this thread or in any of my companies' code. Is there a reason why the XML example isn't applied more often for that speed benefit?

Lawrence

battelofhalfwits Old Hand Points: 369 More actions · Answer 6

Also, when you say "Imagine scaling the cursor to tables with millions of rows". I'm imagining it! Are you sure that inserting the rows into a temporary table with no index and deleting each row, in a loop, on an individual basis, and selecting the count(*) from the table for each row would actually be quicker than using a cursor to just scan through a recordset?

Yes

Also, you can add an Index to a temp table. Also when working with temp tables know which is the better way to declare a temp table @Temp or #Temp. You can get away with @Temp for small datasets, but if you are going to be hitting 1000's of rows then use #Temp.

I work on a platform that requires 24/7 uptime; you really need to know how to be careful when updating and re-working table structures.

Imagination is more important than knowledge.

– Albert Einstein

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

Jeff Moden (1/1/2009)
One of my fellow MVP's... Go back and look at Grant's posts... you'll figure it out. I've probably said too much, already.

When you say "fellow MPV's" does that imply that you are one too? I couldn't find your name listed on the Microsoft site: https://mvp.support.microsoft.com/communities/mvp.aspx

Found the blog:

http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx

His main case for deprecating the FROM clause seems to be portability between different database vendors.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

battelofhalfwits (1/1/2009)
Also, when you say "Imagine scaling the cursor to tables with millions of rows". I'm imagining it! Are you sure that inserting the rows into a temporary table with no index and deleting each row, in a loop, on an individual basis, and selecting the count(*) from the table for each row would actually be quicker than using a cursor to just scan through a recordset?
Yes

You seem pretty sure. Have you tested it?

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Jeff Moden (1/1/2009)
npfitzner (1/1/2009)
Tom.Thomson (1/1/2009)
He wants to remove the From clause from the Update statement? Seriously?
No - I think that's what the 😉 was for.
🙂
Yes! He actually want's to delete FROM from UPDATE. The smiley face was an indication of something else this time...

I think you are being a bit unfair to him,Jeff. I found the blog entry and what he apears to be saying is that people writing SQL need to be aware that the FROM clause in update is not in the standard and use it only when it gives a good advantage in performance or clarity over abvailable standard constructs - and I can agree with that, because portability is NEVER a non-issue (MS might go bust and no-one take over SQLS - yes, it's unlikely; MS licensing might become sufficiently expensive and stupid that I need to switch top mySQL to survive - seems quite likely in the long term). He also says that the "merge" clause (in SQL Server 2008) will do anything the FROM clause will do (I'm not sure whether he's right there or not) and that the FROM clause should therfore be deprecated (which means it would be dropped in the third major SQL Derver version after SQLS 2008 if MS stick to a reasonable backwards compatibility policy). . My view is that even if the MERGE clause can give the performance benefits previously obtained using the FROM clause it doesn't provide the same clarity of expression and we should be pushing for the FROM clause to become part of the standard (although it takes at least 20 years to get the standard committees to pick up and push through a suggested improvement unless it has the big guns behind it, and longer [perhaps forever] if one of the big boys thinks it will give another one a competitive advantage). But the MS implementation of FROM in UPODATE (the only implementation) is broken in that it doesn't work in instead of triggers (something that is pretty unpleasant for some of my stuff, where I end up with row by row meta-SQL because FROM can't be used - fortunately on very small rowsets) as he also points out, and if the FROM clause were to be part of the standard that would need to be sorted out.

Tom Thomson

Tom

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Tom.Thomson (1/1/2009)
Jeff Moden (12/31/2008)
Ok... now I'm getting really afraid for you guys... :hehe: you're talking about this like you use cursors and you're listening to the guy who wants to remove the FROM clause from the UPDATE statement... 😉
He wants to remove the From clause from the Update statement? Seriously? That's complete lunacy, would damage the language enough to leave it close to useless for many of the things I do with it. I find it very difficult to beliieve that any sane person with even a small understanding of what SQL is about could advocate such a thing.

1) Tom, can you or anyone else give an example of UPDATEs that cannot be done without using the FROM clause that can be done with it's use?

2) I interacted with Hugo on a number of occassions at last years MVP summit and I can tell you from first hand experience he is a very smart (and thorough) guy. I for one will never dismiss out of hand anything that he blogs about.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

repicurus (12/24/2008)
I would be happy to contribute TWO examples that I do not think could be accomplished WITHOUT cursors.

There are several places where I agree that at least a loop, if not a cursor, would necessarily be used. But there are also many places where people think a set based solution is impossible when, in truth, they are.

I'd be curious to find out what your two examples are, please. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Alexander Kuznetsov (12/24/2008)
Actually in most cases the best alternative is a loop written in a modern language such as C#, not a T-SQL WHILE loop or a CURSOR. Not only you have the advantage of using a convenient modern language, you may get better performance too. I believe that all these WHILE loop vs. CURSOR debates are becoming irrelevant...

Except in cases of RegExReplace, I guarantee you that it won't be faster in nearly every case. The problem is that you've added another layer and have virtually guaranteed a RBAR solution. Yes, behind the scenes, SQL Server is all RBAR... but it's at the compiled machine language level where the best you can do in C# is go through an API.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

jacroberts (1/2/2009)
Jeff Moden (1/1/2009)
One of my fellow MVP's... Go back and look at Grant's posts... you'll figure it out. I've probably said too much, already.
When you say "fellow MPV's" does that imply that you are one too? I couldn't find your name listed on the Microsoft site: https://mvp.support.microsoft.com/communities/mvp.aspx
Found the blog:
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
His main case for deprecating the FROM clause seems to be portability between different database vendors.

Yes... July 2008. MVP # 4020758. I normally don't brag about it. I guess the problem is this...

"The Microsoft MVP Awardee directory contains a listing of all the MVPs that want to share their information publicly. "

... I never took the time to fill out that information. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

Tom.Thomson (1/2/2009)
I think you are being a bit unfair to him,Jeff.

Heh... Yet, you just agreed with everything I said about UPDATE. 😉 The real fact of the matter is that if you have an UPDATE with a FROM clause, life is so easy you don't need even need MERGE. He also didn't take into account the "quirky update" that makes running totals and "adjacent row data copy" possible until Microsoft actually gets SUM() OVER to work correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 15

Jeff Moden (1/2/2009)
Alexander Kuznetsov (12/24/2008)
Actually in most cases the best alternative is a loop written in a modern language such as C#, not a T-SQL WHILE loop or a CURSOR. Not only you have the advantage of using a convenient modern language, you may get better performance too. I believe that all these WHILE loop vs. CURSOR debates are becoming irrelevant...
Except in cases of RegExReplace, I guarantee you that it won't be faster in nearly every case. The problem is that you've added another layer and have virtually guaranteed a RBAR solution. Yes, behind the scenes, SQL Server is all RBAR... but it's at the compiled machine language level where the best you can do in C# is go through an API.

I think you are wrong on this one Jeff. In the cases where you are replacing a tsql cursor or while loop iteration (which is what I think Alex was espousing) that does simple things like concatenation, sums, etc (NOT updates) the clr loop WILL be faster. See here: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/28/poor-men-see-sharp-ndash-more-cursor-optimization.aspx. Adam is one of the best guys out there for CLR stuff and Hugo's tests seem to bear out Adam's statement that CLR 'cursoring' is the fastest.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service