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


Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations


Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
kathy.m.davis.ctr (4/3/2014)
Thank you for the excellent information! Is there a way to order the personnel in each level by something besides EmployeeID? My user wants to specify the order of the personnel in each level. For example, in level 2, instead of ordering Lynne, Bob, Ken and Marge, he wants Marge, Bob, Lynne, Ken. He wants to do this for each level. Our table has a column UICOrder (int) where the user enters the order of how he wants the items in each level to appear. Your solution in this article works for me except for the ordering of the levels. Any assistance is greatly appreciated!

Thank you,

Kathy Davis


I'll have to take a look at this after work tonight, Kathy. And, thanks 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.
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
kathy.m.davis.ctr
kathy.m.davis.ctr
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 115
Jeff,

Thank you soooo much! I have been working on this all week and have not been successful.

Kathy
kathy.m.davis.ctr
kathy.m.davis.ctr
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 115
Hi Jeff! Have you had time to look at my problem?

Thanks,

Kathy Davis
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
Apologies. I really had to think about this.

First, I guess I don't understand why you would need an order in the output. The output bears no resemblence to a graphical org chart. It's all vertical data that most users won't ever see in that form.

The second thing is that if your REALLY need it in a given vertical order, then you can't use EmployeeIDs either as the child or parent ID. Instead, you'd need to predetermine what the order would be according to the graphical org chart and assign a position number to each node. The child and parent IDs would then be based on those positions and you would simply add the information for the employee holding that current position. The good part about that is that it becomes stupid simple to change someone in a position. The bad part is, you have to know what the org chart should look like so that you can assign the positions and the parents of each position.

That's about the only way I can think of doing this without it being by EmployeeID or by Name.

--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
kathy.m.davis.ctr
kathy.m.davis.ctr
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 115
Jeff,

Thank you so much for your assistance. I will use your advice!

Kathy
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
Thanks for the feedback, Kathy. If you get a chance, stop back and let us know how it worked out. I think originally deciding how to create the positional ID notation might be a bit of a chore but then maintenance becomes easy after that.

--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
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7251 Visits: 6431
Jeff Moden (11/15/2012)

I hear ya!!! If the final PreaggregatedHierarchy table is the source of information for a 24/7/.999999999 web site or application, then you really can’t afford even the 62 seconds of downtime especially if you’re running a world wide Multi-Level Marketing company! You really don’t want to make any of the members angry with even a minute of “downtime” while you rebuild the table.

So what do you do?

It’s simple. Have a synonym (or pass-through view) pointing at the current version of the pre-aggregated hierarchy table while you build up a new version. Once completed, simply repoint the synonym or view to the new table and drop the old table (or keep it to do monthly comparisons for a month). The total “downtime” is now measured in milli-seconds.

Next month, you do the same thing. Just keep “flip-flopping” the synonym or view between the two tables.

You can even include the building of the Primary Key in an online fashion like this IF you let the system name the PKs for you instead of adding them as a “named constraint”. Adding the other indexes are a piece of cake because index names don’t need to be unique in a database like constraints need to be.



Hi Jeff,

I was wondering if you could elaborate on the above with some code examples or references.

Now that I seem to understand your Hierarchies on Steroids after about 3 readings, I'm anxious to learn more.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
Thanks for the question, Dwain. It's real simple.

Assuming that you already have a hierarchy table (We'll call it "H" for this example and all names will be greatly simplified. Substitute as your heart desires) that you want to update...

1. Rename the hierarchy table from "H" to "H1".
2. Create a synonym called "H" and point it at "H1". The "H" synonym takes the place of the table in all "user" code and procs without any changes.
3. Create another hierarchy table called "H2" even if it's empty.
4. Create another synonym called "HW" (hierarchy work) and point it at "H2".

At this point in time, we have synonym "H" pointing at the currently "online" data in table "H1".
At this point in time, we have synonym "HW" pointing at the currently "offline" table "H2".

The time comes for a rebuild of the hierarchy. Here are the steps for that. The final result is that the hierarchical data was unavailable for only milliseconds even though the rebuild may have taken a minute.

1. Run the code to rebuild the "H2" table through the "HW synonym. Except for the table truncate, the prevents the need for all other dynamic SQL for the rebuild.
2. Once "H2" has been successfully rebuilt, simply repoint the "H" synonym at "H2", which contains the new current data to bring it all "online". Total downtime is whatever it takes to drop and rebuilt just the "H' synonym.
3. Repoint the "HW" synonym to the now old data in "H1". You can either keep "H1" for "previous value" comparisons, or truncate it.
4. Next update, you simply rebuild "H1" through the "HW" synonym, repoint the "H" synonym to the "H1" to bring the updated data online, and repoint the "HW" synonym to "H2" in preparation for the next rebuild.

--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
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7251 Visits: 6431
Jeff Moden (3/20/2015)
Thanks for the question, Dwain. It's real simple.

Assuming that you already have a hierarchy table (We'll call it "H" for this example and all names will be greatly simplified. Substitute as your heart desires) that you want to update...

1. Rename the hierarchy table from "H" to "H1".
2. Create a synonym called "H" and point it at "H1". The "H" synonym takes the place of the table in all "user" code and procs without any changes.
3. Create another hierarchy table called "H2" even if it's empty.
4. Create another synonym called "HW" (hierarchy work) and point it at "H2".

At this point in time, we have synonym "H" pointing at the currently "online" data in table "H1".
At this point in time, we have synonym "HW" pointing at the currently "offline" table "H2".

The time comes for a rebuild of the hierarchy. Here are the steps for that. The final result is that the hierarchical data was unavailable for only milliseconds even though the rebuild may have taken a minute.

1. Run the code to rebuild the "H2" table through the "HW synonym. Except for the table truncate, the prevents the need for all other dynamic SQL for the rebuild.
2. Once "H2" has been successfully rebuilt, simply repoint the "H" synonym at "H2", which contains the new current data to bring it all "online". Total downtime is whatever it takes to drop and rebuilt just the "H' synonym.
3. Repoint the "HW" synonym to the now old data in "H1". You can either keep "H1" for "previous value" comparisons, or truncate it.
4. Next update, you simply rebuild "H1" through the "HW" synonym, repoint the "H" synonym to the "H1" to bring the updated data online, and repoint the "HW" synonym to "H2" in preparation for the next rebuild.


Thanks for the explanation Jeff.

When you reset the synonyms like this, does that cause cached query plans to be recompiled?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
I've never had a performance problem because of such switches but if I were forced at gunpoint to make a conjecture, I would unfortunately still have to say I don't know because I've never tested for that.

I can, however, tell you that SQL Server will prevent the drop/rebuild of the synonym if someone is still using it. THAT I've tested for! It will sit and patiently wait to get exclusive use.

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