Harriv,

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

A couple of notes.

1 - the Firebird optimizer is not yet smart enough to transform/re-state sub-selects into more efficient queries, you need to do that. The "... IN (SELECT" needs to be re-stated as " EXISTS ( SELECT 1"

2 - the SQL for your "PSM.LFT BETWEEN (SELECT " is the most circular SQL I have perhaps ever seen.

3 - It generally bad form to mix aliased and non-aliased table references as you did with POSITION_SCREEN_MAP, if you use an alias always use alias for any other references.

Based on the above, I think that the following SQL will work,

SELECT
*
FROM
TEXT_DATA T
WHERE
EXISTS (
SELECT 1
FROM POSITION_SCREEN_MAP PSM
WHERE
(PSM.POSITION_TAG = T.POSITION_TAG)
AND EXISTS (
SELECT 1
FROM
POSITION_SCREEN_MAP MAP2
WHERE
(MAP2.POSITION_TAG = 'AREA1')
AND (MAP2.LFT >= PSM.LFT)
AND (MAP2.RGHT <= PSM.LFT)
)
)