SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row-By-Row Processing Without Cursor


Row-By-Row Processing Without Cursor

Author
Message
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15130 Visits: 9518
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."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88198 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88198 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
poornima.s_pdi
poornima.s_pdi
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 129
Hi,
Your post satisfied my need.Once again 'Thank u' for ur timely help.
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15130 Visits: 9518
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."
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18992 Visits: 14900
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. Wink


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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
DragonGod
DragonGod
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 75
Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88198 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DragonGod
DragonGod
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 75
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
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88198 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search