Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

Question

Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

August 10, 2020 at 10:50 am

This was removed by the editor as SPAM
September 3, 2020 at 4:13 am

This was removed by the editor as SPAM
September 3, 2020 at 4:13 am

This was removed by the editor as SPAM
September 12, 2020 at 6:19 pm

This was removed by the editor as SPAM

Viewing 10 posts - 91 through 99 (of 99 total)

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

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

There is no hierarchical REFERENCES clause in SQL Server. If that's not what you meant, the only other thing that I can think of is some form of DRI and I'm not sure how that would help. Could you explain what you meant?

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

I don't want a hierarchical REFERENCES clause (I am not even sure what that would mean) . But by putting the nodes in one table that reference and the nodes in a separate table, I can have several structures. So one table might be "Personnel" for the company as a whole and then would have a second table that aggregates the employees into "baseball teams", particular projects or whatever. This has the advantage that when an employee is removed from the company, this will also cascade over to the baseball teams, project teams, or whatever. The best way to do this is an ON DELETE SET NULL or ON DELETE SET DEFAULT subclause, but it means you had to do the subordinate hierarchies with careful declarations.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Ah... so you meant a REFERENCES clause in a bit of DRI. Understood. I wasn't getting that from your post.

As attractive to some as it is, I'm not likely to ever use an ON DELETE SET NULL or ON DELETE SET DEFAULT because they simply allow too many people to make mistakes.

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

isaac.arellano Newbie Points: 1 More actions · Answer 4

Hi, excellent post, I have taken it and put it into practice, and it works very well for me to redo the whole nested set,

But I want to ask if you have a way (store procedure or function) for when a new record is created in the adjacent list, and avoid rebuilding the whole set every time a new record is added in the adjacent list.

Thanks in advance

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

isaac.arellano wrote:

Hi, excellent post, I have taken it and put it into practice, and it works very well for me to redo the whole nested set, But I want to ask if you have a way (store procedure or function) for when a new record is created in the adjacent list, and avoid rebuilding the whole set every time a new record is added in the adjacent list. Thanks in advance

Thanks for the feedback. Yes, there is a way to update a "single" new row but it's pretty nasty because you have to add 1 to some Bowers and 2 to the Bowers on ALL the nodes to the logical "right of the new node.

IIRC, Joe Celko covers the method for inserting, deleting, and moving a single node in Nested Sets in his book "Trees and Hierarchies in SQL for Smarties" but you can also find how to do it by search for the task on Google. I only studied the method long enough to know that I didn't want to ever have to do something like that. I'm not sure that there would be a time savings but I've never tested such a thing for performance.

I did rerun all the tests from my original article on newer 64 bit equipment rather than the old 32 bit equipment I originally tested with for the article. The full up million row hierarchy conversion, which leaves you with the original Adjacency list intact, creates a Hierarchical Path hierarchy, and creates the Nested Sets only took 19 seconds instead of the original 54 with no changes to the code. That retest was about 5 or 6 years ago, IIRC. Should be even better on more recent machines.

Also, in case you've not seen it, you may be interested in Part 2 of that short series where most calculations that people would do on a hierarchy are already done and stored in a table. Here's the link for that article.

https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

Thank you again for the feedback.

Oh... and you don't have to drop the original tables to update them in either case so that any "downtime" is limited to the amount of time (low milliseconds close to zero) to simply repoint synonyms to the ONLINE table and the OFFLINE table. Build the new hierarchy in an offline table and when it's done, do the synonym repointing. Just reverse the synonyms the next time. I lovingly call it the "Swap'n'Drop" method. I'm not the original inventor of that method but I use it for a whole lot of things. For example, I have a nightly reload of about 30 tables (funny that I never actually counted how many) that uses the method. As they say in the Navy... "It works fine, fails safe, and drains to the bilge". 😀

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

Y.B. SSChampion Points: 11649 More actions · Answer 6

isaac.arellano wrote:

Hi, excellent post, I have taken it and put it into practice, and it works very well for me to redo the whole nested set, But I want to ask if you have a way (store procedure or function) for when a new record is created in the adjacent list, and avoid rebuilding the whole set every time a new record is added in the adjacent list. Thanks in advance

I just wanted to add to Jeff's response. I've used this technique to update my organization's hierarchy for years. While the update is pretty quick, too many systems rely on the table, so I don't want any downtime. I use the table switch method which is normally used to switch partitions (enterprise feature) but you can switch the whole table instead which will work on any edition.

Regardless of the method you choose, I'd recommend rebuilding the sets whenever there is an update. I find it to be less complicated and error-prone.

SELECT quote FROM brain WHERE original = 1
0 rows returned