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 «««1234»»

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 7: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 @ 12:56 PM
Points: 36,958, Visits: 31,466
Michael Meierruth (11/16/2012)
Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.


2:48 to be precise. And Dwain has it spot on. I live in the area of Detroit. I like to get on the forums to relax a bit after work and work frequently has me up quite late.

We ran into a major problem with a 3rd party upgrade to their software and I had to fix that, which took a good portion of the day. Then we ran into a privs problem with another 3rd party bit of software and trying to figure out what they really needed took a good while. Of course, they wanted "SA" privs and, of course, I'm hell bent on giving them only what I think they really need. I'll never give a 3rd party "SA" privs even on a dev box. After that, I had to fix the point-in-time backups on the dev and staging boxes (seems like overkill but I protect my developers' work as much as I can). We have more disk space on order but it's not in time for the sudden growth we experiennced because of several new clients and all the major DB backups simply ran out of room.

Then, there are the normal daily questions and special requests that need to be handled. For example, I peer review 100% of the code that moves from dev to staging and do a final check when it moves from staging to production. The peer reviews are also time to "mentor" the developers in the art of writing good, high performance SQL and time for me to keep up with all the projects so I know what's coming up for the production databases.

None of that means that I can avoid the work that I need to do on another time-sensitive project and I didn't get started on that until quite late in the day.

Heh... so, yes... to answer the underlying question, I have a hell of a sleep deficit going on most weeks and I'm slightly insane. I need to learn from my own joke about the difference between being "loyal" to a company and being "dedicated". You have to think about "Ham'n'Eggs".... the chicken was loyal... the pig was dedicated.


--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 #1385683
Posted Friday, November 16, 2012 7:08 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 36,958, Visits: 31,466
Jason-299789 (11/16/2012)
Jeff,

As with the first article its top draw, though I'm still reading through them both and making the various links to previous experiences, I like the use of the rollup in the aggregation, a function that often gets overlooked.

I'm going to try and use this to redesign the hierarchy builder I have to see if I can optimise the code.


"Impending usage" because of an article is one of the highest compliments someone like me could hope to have. Thank you very much, Jason. Let us know how it turns out.


--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 #1385686
Posted Friday, November 16, 2012 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 114, Visits: 548
Jeff -

Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.

I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.

- Les
Post #1385778
Posted Sunday, November 18, 2012 9:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 1,945, Visits: 2,898
I'd read about nested sets some time ago but they looked too unwieldy to maintain.


It is funny, but old farts like me want to mimic the pointer chains we had in proceudral languages. So we use cursors (the recursive CTE is really a loop and hidden cursor) and other tricks to get us back to a familar mindset.

When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all. Since the tree structure is separated from the nodes and the rows are short and clustered on (lft, rgt) in a covering index, performance is quite good. You use procedures to do the basic tree operations so you only code it once. If you do the DDL correctly, you will not get orphans and the other problems that plague adjacency list models.

Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port. The roll up total, tree level (root =1) and subtree size with simple aggregates and self-join.

SELECT O1.emp_name,
SUM(CASE WHEN O2.lft BETWEEN O1.lft AND O1.rgt
THEN O2.sale_amt ELSE 0.00 END) AS sale_amt_tot,
SUM(CASE WHEN O2.lft BETWEEN O1.lft AND O1.rgt
THEN 1 ELSE 0 END) AS subtree_size,
SUM(CASE WHEN O1.lft BETWEEN O2.lft AND O2.rgt
THEN 1 ELSE 0 END) AS lvl
FROM OrgChart AS O1, OrgChart AS O2
GROUP BY O1.emp_name;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1386055
Posted Sunday, November 18, 2012 10:03 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 36,958, Visits: 31,466
CELKO (11/18/2012)

When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all.

Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.


There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.


--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 #1386063
Posted Sunday, November 18, 2012 10:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 23,237, Visits: 31,932
Jeff Moden (11/18/2012)
CELKO (11/18/2012)

When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all.

Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.


There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.


Jeff, the problem is Mr. Celko. He thrives on the unprofessional discourse that his on-line persona causes. I really wish we could just get him to go away.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1386070
Posted Sunday, November 18, 2012 11:15 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 36,958, Visits: 31,466
lnoland (11/16/2012)
Jeff -

Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.

I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.

- Les


Thanks for the geat feedback, Les. I really appreciate it.

If you do get a chance to apply some of these things to some of the items you have at work, I'd really be interested in finding out what you did and any problems you may have had to overcome. These post-article discussions are, many times, where the real innovation occurs.


--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 #1386075
Posted Monday, December 24, 2012 12:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Nice article.
Post #1399953
Posted Sunday, January 20, 2013 5:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:36 AM
Points: 2,397, Visits: 3,405
Excellent article(s) Jeff!

I emailed you to your ameritech adress last week wondering about when your "nested set killer" will be published. Sorry to say the email bounced so you will have to send me your new email.

Otherwise, the only thing I missed was the heads up about the article being published!
Kudos.

//Peter



N 56°04'39.16"
E 12°55'05.25"
Post #1409267
Posted Thursday, April 3, 2014 7:58 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 4, Visits: 91
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
Post #1557986
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse