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 «««23456»»

Displaying Sorted Hierarchies (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 4:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 329, Visits: 2,241
Jeff, I can't wait for your new articles.

The CTE and sorting aproach I had to deal with a few times and isn't that new to me, but nested sets and such I still have to dig into. Your articles always spin off a nice discussion with new things to learn even on familiar subjecst.

Also looking forward to that new string splitting article you were talking about not to long ago.


Cheers!
Post #1077628
Posted Monday, March 14, 2011 8:58 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
Thanks, Peter. I've brought all guns to bear on the new splitter article so that shouldn't be very much longer.

As a side bar... MAN! I've got a lot to do in the very near future. To coin a phrase, "It's no longer a matter of how many irons I have in the fire. It's now a matter of how many fires I have irons in."


--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 #1077765
Posted Monday, March 14, 2011 9:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 17,628, Visits: 15,487
Jeff Moden (3/10/2011)
CirquedeSQLeil (3/10/2011)
Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.


Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?



No, we don't use the HierarchyID datatype - not at all.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1077805
Posted Monday, March 14, 2011 11:03 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
CirquedeSQLeil (3/14/2011)
Jeff Moden (3/10/2011)
CirquedeSQLeil (3/10/2011)
Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.


Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?



No, we don't use the HierarchyID datatype - not at all.


Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?


--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 #1077860
Posted Monday, March 14, 2011 11:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 17,628, Visits: 15,487
Jeff Moden (3/14/2011)
CirquedeSQLeil (3/14/2011)
Jeff Moden (3/10/2011)
CirquedeSQLeil (3/10/2011)
Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.


Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?



No, we don't use the HierarchyID datatype - not at all.


Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?


Two-fold. In our testing it seemed that the heirarchyid actually slowed us down a bit. Second - the change would require client sign-off/buy-in. If a client really wanted it, the legacy code could be changed - but they would need to work it into the release schedule and prioritize it. Other things always seem more important and the performance we see for our trees is really good (still need to see your method you were working on for the PASS preso).




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1077870
Posted Monday, March 14, 2011 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 3:02 AM
Points: 18, Visits: 48
Our dev team recently needed to develop a complete solution for a Multi-Level Network Marketing company. They needed to provide a set of reports and views for all of their members based on the signed-in user's level.

Having been in this end of the woods before I immediately started using the CTEs with an adjacency model. The problem that we started to experience in our planning and testing was that we had no idea how deep an international networking marketing company can actually go. The result is that the number of CTEs and related views and related table-valued functions started becoming awkward to manage.

After a few days of serious head-butting with the traditional hierarchy solutions I eventually caved in and started to investigate the new HierarchyID data type - and was pleasantly surprised. In essence it is a replacement for a lot of our own code to find the parent records, find the full path of the current record and a host of other things.

What I liked was the simplification of the code and the fact that the HierarchyID data type allowed for a clustered index, a depth-wise or breadth-wise index and it allowed for reasonably quick extraction of data.

We have not yet done extensive performance testing on the solution and are still concerned about the fact that the maximum size for the HierarchyID column is 982 bytes. Now 982 sounds VERY big, but it may not be big enough to cope with a multinational multi-level network marketing solution that has been running for about 3 years and growing exponentially.

I'm guessing that SQL 2008R2 has optimised this datatype so that it would provide better all round performance in this type of solution to the traditional CTE approach, but I would love to hear "Truth from the field" accounts.

Jeff, when you are next sitting down and giving some thought to your next episode in this drama, maybe you could consider some of these considerations. I know that our team would appreciate it immensely.
Post #1077922
Posted Monday, March 14, 2011 1:19 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
sean-495500 (3/14/2011)

Jeff, when you are next sitting down and giving some thought to your next episode in this drama, maybe you could consider some of these considerations. I know that our team would appreciate it immensely.


The truth be told, I'm working on a large article with MLM's and other large hierarchies in mind. For what the new methods I have instituted are, I believe it will easily satisfy your needs and pretty much blow away methods that use the HierarchyID data.

I'm certainly not ready to publish the article but I do have a test bed which resolves the 4 most request questions of a Uni-level MLM for a million member hierarchy in less than 2 minutes in a very demonstrable fashion. If you care to, please send me an email through this site. I'll also have to state that since this would be direct work, a box of some very nice steaks may be in order.


--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 #1077963
Posted Monday, March 14, 2011 1:23 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
CirquedeSQLeil (3/14/2011)
Jeff Moden (3/14/2011)
CirquedeSQLeil (3/14/2011)
Jeff Moden (3/10/2011)
CirquedeSQLeil (3/10/2011)
Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.


Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?



No, we don't use the HierarchyID datatype - not at all.


Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?


Two-fold. In our testing it seemed that the heirarchyid actually slowed us down a bit. Second - the change would require client sign-off/buy-in. If a client really wanted it, the legacy code could be changed - but they would need to work it into the release schedule and prioritize it. Other things always seem more important and the performance we see for our trees is really good (still need to see your method you were working on for the PASS preso).


To be honest, I've not tested for performance using HierarchyID's but the internet seems somewhat littered with similar complaints of performance. In the short term, I can make the same offer to you that I did Sean above. Send me an email if you'd like.


--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 #1077966
Posted Monday, March 14, 2011 4:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:59 PM
Points: 542, Visits: 2,122
What sort of hierarchy breaths and depths are people running into in the real world?
Numbers please!
Thus for a large telephone company I deal with, I have a table of 300 nodes at 5 levels.
Each terminal node deals with one or more fairly high level objects such as a company project above a certain cost.
Of course, starting at each such a node you typically start a new tree with lots of details - typically 50 more nodes at 3 levels.
But the main tree and sub-trees are dealt with separately.
And this is where the fun starts.
So what sort of hierarchies are people dealing with in the real world?
Is it really zillions of nodes?
Or is it a couple of hundred most of the time?
Post #1078037
Posted Monday, March 14, 2011 4:28 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
Michael Meierruth (3/14/2011)
What sort of hierarchy breaths and depths are people running into in the real world?
Numbers please!
Thus for a large telephone company I deal with, I have a table of 300 nodes at 5 levels.
Each terminal node deals with one or more fairly high level objects such as a company project above a certain cost.
Of course, starting at each such a node you typically start a new tree with lots of details - typically 50 more nodes at 3 levels.
But the main tree and sub-trees are dealt with separately.
And this is where the fun starts.
So what sort of hierarchies are people dealing with in the real world?
Is it really zillions of nodes?
Or is it a couple of hundred most of the time?


MLM companies can have memberships of millions of nodes several hundred levels deep and very wide fanouts in the dozens or more.

Other than such monsters, the HeirarchyID will very easily accomodate some very large hierarchies. I can't vouch for the performance yet but the rumor mill on the internet seems to indicate that it's a bit slow.


--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 #1078041
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse