Displaying Sorted Hierarchies (SQL Spackle)

Question

Displaying Sorted Hierarchies (SQL Spackle)

Viewing 15 posts - 31 through 45 (of 59 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

CELKO (3/10/2011)
Jeff, GSquare, et al
I am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP.
Think of the fame, the glory, the books and beer that comes with it!
For the record and the other readers:
To find the level of each emp_name, so you can print the tree as an indented listing.
SELECT T1.node,
SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END
+ CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl
FROM Tree AS T1, Tree AS T2
WHERE T2.lft <= T1.lft
GROUP BY T1.node;
An untested version of this using OLAP functions might be better able to use the ordering. Ut will not work in T-SQL becasue we don't have the RANGE sub-clause yet.
SELECT T1.node,
SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END
+ CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END)
OVER (ORDER BY T1.lft
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lvl
FROM Tree AS T1, Tree AS T2
WHERE T2.lft <= T1.lft;

For levels in nested sets, I've usually just found it easier to store that in the table, generated when you generate the boundary values. The whole idea of nested sets hierarchies, in my opinion, is to hard-store the values you'll need so that queries require the absolute minimum of runtime math. Sort of like a warehouse that way.

Edit: I just took a closer look at that code, and it looks like a triangular join (classic "running total" issue). Correct me if I'm wrong on that, Joe. If it is what it looks like, it'll have similar performance-killing properties to any other triangular join, and you're probably better off going to a simple adjacency model and recursive CTE, than using that. Recursion is less expensive in most cases than triangles are.

As for data on hierarchies that you can include, you've seen my hierarchies article on this site and the discussions about it (you replied in the discussion). So long as you cite it, and Red Gate/Steve have no issues with it, you're free to use whatever you like from that. I don't publish online unless I expect something to be used by others. (IP protection is a laughable subject these days, in my opinion. Same as privacy. They're both myths.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

TheSQLGuru (3/10/2011)
I use 'generated' TSQL like that all the time but never thought to make the leap and intermix straight/generated TSQL in a CTE like that. Oh the possibilities!! Nicely done Jeff, as usual!

I wish I could take the credit for such an ingenious thing but the "generated" TSQL that I believe you're citing in the article isn't actually "generated". It's a mistake of the "prettifier" this site has that inappropriately colored some of my code as if it were a string. I'll try to fix it so that folks are not misled into thinking that I did some sort of magic here. :blush:

I do, however, greatly appreciate the feedback.

--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: 1004432 More actions · Answer 3

gregg_dn (3/10/2011)
Great article Jeff.

Thanks for stopping by, Gregg. I appreciate the compliment.

--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: 1004432 More actions · Answer 4

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?

--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: 1004432 More actions · Answer 5

sean-495500 (3/10/2011)
Nice article Jeff. I see that you promised a more in-depth/advanced discussion within the body of your article. Are you going to be discussing the HierarchyID data type in that article?
I think that this demonstration piece of code shows the value of CTEs - and is great for small HR type applications that do not required scalability, but there is huge benefit to using the HierarchyID data type as an additional column or even to replace the Primary Key value with.
Perhaps your next article could explore these alternatives?

My apologies... I missed this post earlier. I didn't really want to cover anything about the HierarchyID datatype, how to use it, or a performance comparison but I would be remiss if I left it out.

As a side bar, the method I've shown in this "SQL Spackle" article works quite well (absolutely not as good as "Nested Sets") even on huge hierarchical databases especially if one indexes the parent/child relationship properly. As I said before, though, I can't really address the performance of the HierarchyID datatype or it's SQLCLR based functionality because I've simply not used it. I tend to take the time to convert to the "Nested Set" method. I will, however, be sure to test all of that.

Thanks again for the feedback. 🙂

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

JunkIt SSCommitted Points: 1554 More actions · Answer 6

In its implementation of recursive CTEs, DB2 has a "Search Depth First" option that can be specified to return rows in this "drill down" fashion. I've been looking for a way to emulate this behavior in SQL Server (without using Hierarchy IDs or a CLR table function) and now I've found it!

Thanks for this article.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

JunkIt (3/11/2011)
In its implementation of recursive CTEs, DB2 has a "Search Depth First" option that can be specified to return rows in this "drill down" fashion. I've been looking for a way to emulate this behavior in SQL Server (without using Hierarchy IDs or a CLR table function) and now I've found it!
Thanks for this article.

Very cool! Thank you for the feedback.

I do have to say again, this is a "quick'n'dirty" method to get someone out of the woods. The use of "Nested Set" technology will be much higher in performance even for huge hierarchies. I don't know if you can wait for it but I'm currently writing an article on how to very quickly make the conversion which can be permanent or just long enough for a short query like the one in this article.

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

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 8

Jeff is the best as always! Always look forward to his "SQL Spackle" articles. Thank you!

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

Very cool compliment. :blush: Thanks Mishaluba!

I aim to please. I sometimes miss but I'm always aiming. 😀

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 10

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

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.

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 13

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.

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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[/url]
Learn Extended Events

SeanAchim Right there with Babe Points: 764 More actions · Answer 15

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.