Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Delete Duplicate values Expand / Collapse
Author
Message
Posted Saturday, October 11, 2008 11:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
Outstanding explanation, Kenney.

On the recursive thing... do keep in mind that recurssion is nothing more than a loop and has the same performance impedements as a loop. In other words, it IS a form of RBAR. There are many ways to avoid the use of any type of RBAR on many things. Even when it comes to Hierarchies, there are some really good ways to precalculate the hierarchy (nested sets) and then do the lookups using set based technologies instead of RBAR.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #584410
Posted Saturday, October 11, 2008 10:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:55 AM
Points: 319, Visits: 158
Thank you Jeff.

And thanks for pointing out the RBAR aspect of recursive queries. I'm still not very good at spotting hidden RBAR, and you are the master of that subject. :) I remember reading an article by you about triangular joins being RBAR, it was very enlightening.

Do you happen to have a posted example of a non-RBAR hierarchal query? I'd really like to see how that is done. I've been very impressed by your postings that I have read.

Thanks again,
Kenney
Post #584499
Posted Monday, October 13, 2008 8:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
Kenny,
Thanks for pointing out the performance implications of using a CTE versus a temp table multiple times in a query.
Post #584876
Posted Monday, October 13, 2008 8:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
Kenney Hill (10/11/2008)
Thank you Jeff.

And thanks for pointing out the RBAR aspect of recursive queries. I'm still not very good at spotting hidden RBAR, and you are the master of that subject. :) I remember reading an article by you about triangular joins being RBAR, it was very enlightening.

Do you happen to have a posted example of a non-RBAR hierarchal query? I'd really like to see how that is done. I've been very impressed by your postings that I have read.

Thanks again,
Kenney


You bet...

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
http://www.codeproject.com/KB/database/nestedsets.aspx

What I end up doing is preserving both models on hierarchies that don't change much (most don't). People have an easier time maintaining and thinking about the adjacency model and the code runs better on the nested set model.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #584916
Posted Friday, September 11, 2009 3:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
Abhijit,
Great Article.

Moreover, nicely explained and modified by Jeff.
Really appriciate.
Post #786219
Posted Friday, September 11, 2009 4:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
Thank you for the feedback... Kenny actually did all the heavy lifting, though.

Thanks, Ken.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #786755
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse