Re: improvements to query with hierarchical elements

At 07:24 PM 1/22/2008, you wrote:
>Hi all,
>
>I have created a little test database to help illustrate my situation.
>
>CREATE TABLE categories (
> id integer NOT NULL,
> name character varying(255) NOT NULL,
> description character varying(255),
> vocabulary_id integer,
> derived boolean
>);
>
>CREATE TABLE category_descendants (
> id integer NOT NULL,
> ancestor_id integer,
> descendant_id integer,
> distance integer,
> derived boolean
>);
>
>CREATE TABLE category_links (
> id integer NOT NULL,
> parent_id integer,
> child_id integer,
> derived boolean
>);
>[snip..]
>As stated in my last post, any help you can give on how to improve
>queries of this type would be very much appreciated.
>
>Thanks!
>Ryan
>

Hi Ryan,

I've been toying with your sample data for a bit and I apologize but
your query has me baffled. Not that it's wrong - it actually looks very
sophisticated, but it seems super complex to me - kind of like how I
usually feel reading perl.. :)

I'm sure real sql-heads would get it right away but I'm not able to.

If you're looking to optimize the use-case you provided in your first
email, the best thing I can suggest from what I understand would make
an assumption:

Are the data in your tables are slowly changing? So could you build
some analytic/pre-calculated data into these tables or related
supporting ones to guide your searches/queries?

For example, if you want to find only records which are immediate
children of other records, why not make a table which stores just that
information? Your current tables are fully hierarchical which is great,
but you want to look things up quickly based on a specific
relationship: records who are direct children of a particular record..

So if you made a calculated table that stores this information, you
could keep it up to date either by running the calculation script
periodically or by attaching updates to relevant triggers / rules.

I'm sorry I'm not able to get into the SQL / example you sent further.
I got lost in the code, which I'm a little embarrassed to admit but
there you are.

If you're interested in this idea of precalculating values to optimize
your search, I'd be happy to discuss further. Also, Ralph Kimball's
Data Warehousing books are excellent on this subject (one of the few
authors who truly changed the way I think about data).

Steve