Displaying Sorted Hierarchies (SQL Spackle)

Question

Displaying Sorted Hierarchies (SQL Spackle)

Viewing 15 posts - 46 through 59 (of 59 total)

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

Jeff Moden SSC Guru Points: 1004340 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: 1004340 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: 1004340 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: 1004340 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: 1004340 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: 1004340 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: 1004340 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 Veteran Points: 214 More actions · Answer 12

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

denisribeiro wrote:

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

Sorry I missed this way back when. If you're still around, let me ask you 2 questions...

What would be the purpose of sorting this in Descending order? I ask because I honestly can't think of a good reason.
Looking at the code for the Ascending order examples, we find the following in the outer SELECT...

  ORDER BY HierarchicalPath

Wouldn't you just change that to the following give you what you need? 🙂 (EDIT: Maybe to display the "upline" as a downline?)

  ORDER BY HierarchicalPath DESC

--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: 1004340 More actions · Answer 15

sholliday wrote:

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

Again, apologies for the miss all these years ago. The forum did have problems with notifications way back and 2013 seems about the right time but don't know for sure.

Also, a couple of other seriously important "Hierarchies on Steroids" articles have come out since this original article. If you're still around, you might want to have a look at those. On modern equipment, the million node hierarchies take on 19 seconds to convert instead of the 54 seconds originally stated in the article.

To answer the previously missed question...

Yes, but I wouldn't do it 100% "on-the-fly". Hierarchies don't change that frequently and so I'd "materialize" the hierarchical results that we've been creating, much like you would for financial data in a data warehouse.

Then, you can do anything pretty easily...

First, materialize the hierarchy to a table. I use a Temp Table here... you should use a semi-permanent table, like other tables in a data warehouse. Here's the code I used in the "names" example from the article with a couple of additions (look for comments in the code)

DROP TABLE IF EXISTS #Hierarchy; --Added this to make reruns easier.
GO
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,
        EmpName      = EmployeeName, --ADDED THIS COLUMN 
        EmployeeLevel,
        HierarchicalPath 
    INTO #Hierarchy --DUMPING THE OUTPUT TO A TABLE. HIERARCHIES DON'T CHANGE FREQUENTLY
   FROM cteDirectReports
  ORDER BY HierarchicalPath
;

Then, this solves the problem by splitting the sort path for Megan and finding the "upline". This could also be done with a recursive CTE but that would probably be more expensive that the split'n'join.

Since this was back in the days of 2012, here's the link to the DelimitedSplit8K function, which is in the "Resources" section at the end of the article...

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

Once you have that, the solution doesn't take much... it splits the names and looks up the rows they're on (with the obvious caution about using names for lookups)

   WITH cteUpline AS
(
 SELECT  EmployeeName = split.Item
        ,SortOrder    = split.ItemNumber
   FROM #Hierarchy ul
  CROSS APPLY dbo.DelimitedSplit8K(ul.HierarchicalPath,'\')split
  WHERE EmployeeID = 12
)
 SELECT h.*
   FROM #Hierarchy h
   JOIN cteUpline ul ON h.EmpName = ul.EmployeeName
  ORDER BY ul.SortOrder
;

Here's are the results from that bit of computational prestidigitation...

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