Re: SQL question on hierarchical data

From: Scott Gray <gray_at_voicenet.com>
Date: 1998/10/15
Message-ID: <qJlV1.8914$wV1.5761008@news2.voicenet.com>#1/1

In comp.databases.sybase gus <gus_goose_at_hotmail.com> wrote:

: Scott Gray wrote:

:>
:> In comp.databases.sybase gus <gus_goose_at_hotmail.com> wrote:
:> : eric__hogue_at_my-dejanews.com wrote:
:> :>
:> :> In article <HwAS1.2910$yo1.55067256_at_news.ipass.net>,
:> :> "David Sisk" <davesisk_at_ipass.net> wrote:
:> :> > Sunil Godithi wrote in message ...
:> :> > >hi,

 [snip]
:>
:> Ack! Evil iterative processing! :) Yes...there is another way.
:>
:> Check out:
:>
:> http://reality.sgi.com/pablo/Sybase_FAQ/Q7.7.html

[snip-a-de-doo-da]

: I will have a look  ;-)
: cheers
: gus

Gus,

After re-reading my posting, I realized that it sounded like I was denouncing your algorithm (I was really just trying to be a bit "cute" in my intro...), this was definitely not my intent...and if you got that impression, I appologize.

The two algorithms complement eachother nicely..the one you presented in good for environments in which the structure of the hierarchy changes frequently, or upon which queries are performed infrequently (relative to changes). The one that I presented is good for relatively low update frequency (although, it can be modified, as mentioned in the write-up, to withstand a reasonable amount of updates), but with large and complex hierarchies.

Also, if I can find the write-up I have a relatively nice (but not entirely elegant) middle-ground algorithm between the two that basically "denormilizes" all of the possible "parent-of" and "child-of" pairs into a table, such that there is one row that represents of node x is a child of node y, reguardless of how many levels are between the two. It suffers from exponential explosions of data if the hierarchy grows terribly large, but if it stays small, then the algo is about medium on the search spead and medium on the update speed. I'll see if I can find it and post it...

-scott

-- 
Scott C. Gray                 gray_at_voicenet.com     "my keybard is brken"
Sybase Professional Services  scott.gray_at_sybase.com
   http://www.voicenet.com/~gray/sqsh.html

Received on Thu Oct 15 1998 - 00:00:00 CDT