Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 8:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,098, Visits: 33,964
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558012
Posted Thursday, April 3, 2014 1:19 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4, Visits: 97
Jeff,

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

Kathy
Post #1558209
Posted Monday, April 7, 2014 8:13 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4, Visits: 97
Hi Jeff! Have you had time to look at my problem?

Thanks,

Kathy Davis
Post #1559094
Posted Monday, April 7, 2014 5:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,098, Visits: 33,964
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1559311
Posted Tuesday, April 8, 2014 1:48 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4, Visits: 97
Jeff,

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

Kathy
Post #1559688
Posted Tuesday, April 8, 2014 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,098, Visits: 33,964
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1559769
Posted Thursday, March 19, 2015 12:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 3,886, Visits: 6,044
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
Post #1669757
Posted Friday, March 20, 2015 8:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,098, Visits: 33,964
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1670235
Posted Sunday, March 22, 2015 6:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 3,886, Visits: 6,044
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
Post #1670539
Posted Sunday, March 22, 2015 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,098, Visits: 33,964
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1670543
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse