ckarasinir wrote:

> Hello,
>
> I am using a tool that maps relational structures to programmatic
> entities and it generates the SQL automatically.
> When loading a hierarchy of objects it uses the IN clause extensively
> in the following way (the query is simplified)
>
> SELECT LOCATION.LOCATION_ID
> FROM LOCATION
> WHERE LOCATION.LOCATION_ID
> IN (SELECT FINDING.LOCATION_ID
> FROM FINDING INNER JOIN SURVEY_FINDING ON FINDING.FINDING_ID=
> SURVEY_FINDING.FINDING_ID
> WHERE SURVEY_FINDING.SURVEY_ID =
> '536313bd-427e-42db-964e-7a8c9092c455')
>
> The query plan for this type of query both in v1.5 and v2.0 is the
> following
>
> PLAN JOIN (SURVEY_FINDING INDEX (SURVEY_FINDING_SURVEY),FINDING INDEX
> (FINDING_PK)) PLAN (LOCATION NATURAL)
>
> The high-level structure of the tables involved are
> LOCATION(LOCATION_ID PK)
> FINDING(FINDING_ID PK, LOCATION_ID FK)
> SURVEY_FINDING(FINDING_ID FK)
>
> I would think that the optimizer would pick the indexes for LOCATION
> also instead of performing a table scan.
>
> Is there anything I can do? Such as a hint so that Firebird would
> actually use the index instead.
>
> Thank you
> Cihan

I think the optimizer translates your SQL to

SELECT LOCATION.LOCATION_ID
FROM LOCATION
WHERE EXISTS (SELECT * FROM FINDING
JOIN SURVEY_FINDING ON FINDING.FINDING_ID=SURVEY_FINDING.FINDING_ID
WHERE SURVEY_FINDING.SURVEY_ID =
'536313bd-427e-42db-964e-7a8c9092c455'
AND LOCATION.LOCATION_ID = FINDING.LOCATION_ID)

EXISTS is generally not something that is or can be indexed (at least
not in Firebird 1.5). If you want to use an index, you have to change
the SQL, possibly to something like

SELECT DISTINCT LOCATION.LOCATION_ID
FROM LOCATION
JOIN FINDING ON LOCATION.LOCATION_ID = FINDING.LOCATION_ID
JOIN SURVEY_FINDING ON FINDING.FINDING_ID=SURVEY_FINDING.FINDING_ID
WHERE SURVEY_FINDING.SURVEY_ID =
'536313bd-427e-42db-964e-7a8c9092c455'

(this will produce the same result as your original query if
LOCATION.LOCATION_ID is unique).

If the SQL is generated automatically and cannot be changed, then I'm
sorry to say that it cannot be further optimized in Firebird 1.5, and I
doubt it is possible in Firebird 2.0.

Set