Handling hirarchy in tables

Question

Post reply

Handling hirarchy in tables

Viewing 10 posts - 16 through 25 (of 25 total)

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

rashmi.todkar SSC Journeyman Points: 90 More actions · Answer 1

by the way this is also a very good link for nested sets

http://www.codeproject.com/KB/database/nestedsets.aspx

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

rashmi.todkar (7/4/2008)
Thanks all.
I gone through nested sets options its really best one for high performance reads on such data. but update and insert is bit costly however I can afford it for some of my tables.
Let me try CTE option I will get back to you once i finalize the solution.
Once again thanks guys 🙂
regards
Rashmi

And, STILL you haven't answered my very simple question so I can, possibly, help you do something more than the very slow use of recurrsion. I'll ask it one more time... how often does the data change??? :w00t:

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

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

rashmi.todkar (7/4/2008)
by the way this is also a very good link for nested sets
http://www.codeproject.com/KB/database/nestedsets.aspx

That is a good article... graphics for the explanations are spot on and done well.

Heh... It's appropriate that, in this day and age of job cuts, such a mistake as the following should occur in an article about positional hierarchies... 😀

"Nested sets rely on a demoralization of parts of the hierarchy,"

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

Ok... now, I'm impressed! I've not reviewed all of the code in the good article recommended by Rashmi, yet... but the author (James Simpson) has done a strange and wonderful thing that I've always practiced and have tried to incite others to do... document the purpose (the WHY and the WHAT, not the HOW) of each logical "thought" in the code using embedded comments.

The URL for the article bears repeating...

http://www.codeproject.com/KB/database/nestedsets.aspx

Thanks for the great link, Rashmi...

P.S. I still want to know how often your hierarchical data changes 😉

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Somethings wrong with your link, Jeff.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Heh... must be catchy... "Now, how did THAT happen?"... heh... I'm not even wearing a helmet! 😉

Thanks for the catch, Barry. 🙂

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

rashmi.todkar SSC Journeyman Points: 90 More actions · Answer 7

Hi Jeff

here is the answer for your question 😀 See In my database there are 2-3 such hierarchical sets. one is Employee-Manager kind of, another is location and sub-locations, organizational departments and sub-departments bla bla bla.

Some of these may not change frequently but still they will change. see if I go for nested sets -- one manager change for an employee results into lot of record updates, I just want to avoid that delay. So its better to go for CTE option.

regard

Rashmi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

That's what I was afraid of... the Recursive CTE is a quick way out for you. Sure, it works fine, but there has to be something better than constantly recalculating a downline in the tree everytime you need to.

I'm not sure why you think a nested set model is diffult to maintain... just cheat like I do... maintain the adjacency model because it's simple for the brain. Then, just run the code to rebuild the nested set. 😉

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

rashmi.todkar SSC Journeyman Points: 90 More actions · Answer 9

I am worried about the performance of the system...

see My idea is to give tree kind of manage employees screen. I will render all employees as tree and user can just drag and drop the employee from one node to other . In this view, I can afford some delay in rendering tree but cant offord delays in each drag and drop (data updates)

Anyways the ocasions where i need to get all big tree data with many levels are few in comparioson of inserts and updates. and anyways I have data caching options for such kind of situations...

Regards

Rashmi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 10

Sorry... I misinterpreted what you said... I thought you said there weren't that many changes. My mistake.

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