Row-By-Row Processing Without Cursor

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 Amin Sobati » Row-By-Row Processing Without Cursor

71 posts, Page 7 of 8

«««4 5 6 7 8 »»

Row-By-Row Processing Without Cursor

Rate Topic

Display Mode

Topic Options

Author

Message

RBarryYoung

Posted Friday, May 02, 2008 10:32 AM

SSCrazy Eights

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

Jeff Moden (4/30/2008)

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

http://www.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://www.sqlservercentral.com/Forums/FindPost492576.aspx which seems to work better.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #494412

Jeff Moden

Posted Friday, May 02, 2008 6:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #494595

Jeff Moden

Posted Friday, May 02, 2008 6:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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

Post #494596

poornima.s_pdi

Posted Saturday, May 03, 2008 1:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 01, 2013 4:55 AM
Points: 27, Visits: 41

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

Post #494632

RBarryYoung

Posted Sunday, May 04, 2008 1:21 PM

SSCrazy Eights

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

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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #494712

Jack Corbett

Posted Tuesday, September 02, 2008 3:12 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #562700

DragonGod

Posted Saturday, September 12, 2009 3:52 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 5, Visits: 65

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

Post #786920

Jeff Moden

Posted Saturday, September 12, 2009 4:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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

Post #786922

DragonGod

Posted Monday, September 14, 2009 8:18 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 5, Visits: 65

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?

Post #787497

Jeff Moden

Posted Monday, September 14, 2009 9:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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

Post #787862

« Prev Topic | Next Topic »

71 posts, Page 7 of 8

«««4 5 6 7 8 »»

Permissions