Displaying Sorted Hierarchies (SQL Spackle)

Question

Displaying Sorted Hierarchies (SQL Spackle)

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

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 SSC Guru Points: 1003863 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

A bit ignorant here, what's an MLM?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Craig Farrell (3/14/2011)
A bit ignorant here, what's an MLM?

Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Jeff Moden (3/14/2011)
Craig Farrell (3/14/2011)
A bit ignorant here, what's an MLM?
Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.

AH! Legal Pyramid Schemes! Gotcha!

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Craig Farrell (3/14/2011)
AH! Legal Pyramid Schemes! Gotcha!

Heh... I have mixed emotions about them. The ones that run a Uni-Level payout matrix and require you to sell product and not just stack up representatives are some of the best. People CAN make money without being at the "top". The people who fail to run their business correctly (usually boils down to being lazy) seem to get the most press and, of course, they have nothing good to say about the companies.

Don't get me wrong... there are some really bad, bad, bad ones out there, as well but the industry, in general, has a legacy reputation that it no longer deserves.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

@sean,

Did you get my email?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sykiemikey Grasshopper Points: 19 More actions · Answer 10

Excellent article Jeff! You just saved me quite a bit of sanity

I was needing to sort by an alphanumeric name field and finally found a suitable way that doesn't seem to break.

Here it is if anyone else has need of it.

The main difference is the HierarchicalPath.

You could essentially put ANY column in the OVER(ORDER BY) function and get a perfect sort.

Mine assumes I'll never have more than 99999 items.

WITH DirectPrograms (ProgramParent, ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath)

AS

(

SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = 0,

HierarchicalPath = CAST('\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))

FROM ys2.PROGRAM AS p

WHERE ProgramParent IS NULL

UNION ALL

SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = [Level] + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))

FROM ys2.PROGRAM AS p

INNER JOIN DirectPrograms AS d

ON p.ProgramParent = d.ProgramID

)

SELECT ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath

FROM DirectPrograms

ORDER BY HierarchicalPath

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Just in case those that already responded on this thread haven't seen them, the articles that I was talking about writing came out a while ago. Here are the links.

For some very high performance methods for building/maintaining (54 seconds for a million node hierarchy) from an Adjacency List and using Nested Sets in conjunction with both an Adjacency List and a Hierarchical Path all in one table, please see the following article.

[font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/font][/url]

For information on how to build a pre-aggregated hierarchical table (again... only takes about a minute) that answers for most of the things you'd ever query a hierarchical table for (MLM'ers will love this one), please see the following article.

[font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/font][/url]

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

denisribeiro SSC Enthusiast Points: 194 More actions · Answer 12

denisribeiro

SSC Enthusiast

Points: 194

August 24, 2013 at 7:41 am

#1644649

Great post!

So, how can I do this with desc order by?

sholliday SSCrazy Points: 2100 More actions · Answer 13

Is there a way to get the complete hierarchy for a single Employee?

declare @EmployeeID int

select @EmployeeID = 12

;WITH

cteDirectReports AS

(

SELECT EmployeeID, ManagerID, EmployeeName, EmployeeLevel = 1,

HierarchicalPath = CAST('\'+CAST(EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, EmployeeLevel = d.EmployeeLevel + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Employee e

INNER JOIN cteDirectReports d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID,

ManagerID,

EmployeeName = SPACE((EmployeeLevel-1)*4) + EmployeeName,

EmployeeLevel,

HierarchicalPath

FROM cteDirectReports

where EmployeeID = @EmployeeID

ORDER BY HierarchicalPath

;

My quick and futile attempt above (which I understand why it does not work) only gets:

128 Megan4\Jim\Bob\Bill\Megan

The desired result would be:

1NULLJim1\Jim

31 Bob2\Jim\Bob

83 Bill3\Jim\Bob\Bill

138 Kim4\Jim\Bob\Bill\Kim

128 Megan4\Jim\Bob\Bill\Megan

Thanks for the post.