On Sat, Feb 6, 2010 at 2:01 PM, emb_blaster <EMB_Blaster@...> wrote:

>
>
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> Harriv <harriv@...> wrote:
> >
> > Hi,
> >
> > I did small experiment storing hierarchical data, and now I'd like to
> > optimize my query when retrieving tree.
> >
> > I'm using the Modified Preorder Tree Traversal described here:
> > http://articles.sitepoint.com/article/hierarchical-data-database/2
> >
> > My query is:
> >
> > SELECT
> > *
> > FROM
> > TEXT_DATA T
> > WHERE
> > T.POSITION_TAG IN (SELECT
> > PSM.POSITION_TAG
> > FROM
> > POSITION_SCREEN_MAP PSM
> > WHERE
> > PSM.LFT BETWEEN (SELECT
> > POSITION_SCREEN_MAP.LFT
> > FROM
> > POSITION_SCREEN_MAP
> > WHERE
> > POSITION_SCREEN_MAP.POSITION_TAG = 'AREA1') AND (SELECT
> > POSITION_SCREEN_MAP.RGHT
> > FROM
> > POSITION_SCREEN_MAP
> > WHERE
> > POSITION_SCREEN_MAP.POSITION_TAG = 'AREA1')
> > )
> >
> > The plan is:
> >
> > PLAN (POSITION_SCREEN_MAP INDEX (PK_POSITION_SCREEN_MAP))
> > PLAN (POSITION_SCREEN_MAP INDEX (PK_POSITION_SCREEN_MAP))
> > PLAN (PSM INDEX (PK_POSITION_SCREEN_MAP))
> > PLAN (TEXT_DATA NATURAL)
> >
> > If I've understood correctly, IN operator isn't very optimizable, is
> > there way to avoid using it?
> >
>
> hi Harriv,
>
> you could be clearer about what you expecting retrieve?
> reading that paper about Modified Preorder Tree Traversal I see that they
> are using only one table, but you are using 2 different tables (one of them
> in more than one place) but you are not using FK's... so your code is
> strange... can you explain it?
> Also, what version of FB you are using?
>
> Hi,

I've two tables, the "hierarchy table" POSITION_SCREEN_MAP, and then the
actual data, "TEXT_DATA". So basically I've a lot of text blobs I need to
organize in the tree structure. POSITION_TAG is the name of the node in
tree, and it is used to associate one text blob to certain node in the
tree.

I'm using FB 2.1.3, Windows SuperServer.


[Non-text portions of this message have been removed]