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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Hierarchies on Steroids #2: A Replacement for...

29 posts, Page 3 of 3

«««1 2 3

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

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Friday, November 16, 2012 7:04 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

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

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

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1385683

Jeff Moden

Posted Friday, November 16, 2012 7:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

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

Post #1385686

lnoland

Posted Friday, November 16, 2012 10:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 96, Visits: 431

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

CELKO

Posted Sunday, November 18, 2012 9:28 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

Jeff Moden

Posted Sunday, November 18, 2012 10:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

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

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

Post #1386063

Lynn Pettis

Posted Sunday, November 18, 2012 10:49 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

Jeff Moden (11/18/2012)

CELKO (11/18/2012)

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.

Cool

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

Jeff Moden

Posted Sunday, November 18, 2012 11:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

lnoland (11/16/2012)

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

Post #1386075

Neha05

Posted Monday, December 24, 2012 12:03 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55

Nice article.

Post #1399953

SwePeso

Posted Sunday, January 20, 2013 5:13 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292

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

« Prev Topic | Next Topic »

29 posts, Page 3 of 3

«««1 2 3

Permissions