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


Displaying Sorted Hierarchies (SQL Spackle)


Displaying Sorted Hierarchies (SQL Spackle)

Author
Message
peter-757102
peter-757102
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1709 Visits: 2559
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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208415 Visits: 41973
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." :-P

--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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64795 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208415 Visits: 41973
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.
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64795 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SeanAchim
SeanAchim
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 57
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208415 Visits: 41973
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.
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 (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208415 Visits: 41973
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.
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
Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2276 Visits: 2516
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208415 Visits: 41973
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.
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