Row-By-Row Processing Without Cursor

Question

Post reply

Row-By-Row Processing Without Cursor

Viewing 11 posts - 61 through 70 (of 70 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Jeff Moden (4/30/2008)

See the following URL... almost identical situation...

http://qa.sqlservercentral.com/Forums/Topic491969-149-1.aspx#bm492576

Jeff:

FYI, the "Post #" links like this do not seem to be working, they just take to the beginning of the Topic. When I use the Post-links on the lower left-hand side of the posts, it gives me something like this: http://qa.sqlservercentral.com/Forums/FindPost492576.aspx which seems to work better.

[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]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Funny, they work fine for me... they take me right to the post I wanted.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

poornima.s_pdi (5/1/2008)

Hi Jeff,

Thanks a lot for ur reply.I learnt more from ur sites.very good snd useful forum...

Thank you for the feed back, Poornima... are you all set now?

--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)
Intro to Tally Tables and Functions

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 4

poornima.s_pdi

SSCrazy

Points: 2720

May 3, 2008 at 1:06 am

#810412

Hi,

Your post satisfied my need.Once again 'Thank u' for ur timely help.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (5/2/2008)

Funny, they work fine for me... they take me right to the post I wanted.

I think that it has to do with paging. Anchors, like page#postnumber won't work if the topic spans multiple pages and the target post is not on the first page. Since "Posts per Page" is customizable, it might be paging differently for us.

[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]

Jack Corbett SSC Guru Points: 184388 More actions · Answer 6

Jeff Moden (1/2/2008)

The real point here is that you should not be using any form of RBAR in a trigger... no matter how you do it, calling a RBAR proc from a trigger is an insane thing to do... the proc should be rewritten to handle sets of data instead of the slothful agony of single row processing. 😉

Jumping in here way late, but there is no doubt that RBAR processing whether a cursor or while loop in a trigger is a very bad idea. You are better off staging the data and processing it in a separate process whether that's Service Broker or another option.

Something I do not recall seeing mentioned is that you can do a Select Into temp_table from inserted\deleted then access that temp table in a stored procedure called from the trigger where you can do some set-based processing, of course that's only necessary if you want to encapsulate the code so you can re-use it because you can process it the same way in the trigger.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DragonGod Old Hand Points: 396 More actions · Answer 7

DragonGod

Old Hand

Points: 396

September 12, 2009 at 3:52 pm

#1052741

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Roy Oliver (9/12/2009)

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

Why? They are inherently RBAR. "For Each Row".

--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)
Intro to Tally Tables and Functions

DragonGod Old Hand Points: 396 More actions · Answer 9

DragonGod

Old Hand

Points: 396

September 14, 2009 at 8:18 am

#1053235

Jeff Moden (9/12/2009)

Roy Oliver (9/12/2009)

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

Why? They are inherently RBAR. "For Each Row".

True, yet isn't the performance faster than the SQL Server Cursor?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Roy Oliver (9/14/2009)

Jeff Moden (9/12/2009)

Roy Oliver (9/12/2009)

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

Why? They are inherently RBAR. "For Each Row".

True, yet isn't the performance faster than the SQL Server Cursor?

I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.

Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.

--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)
Intro to Tally Tables and Functions

DragonGod Old Hand Points: 396 More actions · Answer 11

Jeff Moden (9/14/2009)

Roy Oliver (9/14/2009)

Jeff Moden (9/12/2009)

Roy Oliver (9/12/2009)

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

Why? They are inherently RBAR. "For Each Row".

True, yet isn't the performance faster than the SQL Server Cursor?

I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.

Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.

Yeah, that's a good point.

Thanks