Thanks Set and Arno.

I also read more postings in the support archive, which I should have
done obviously prior to posting. It looks like there isn't an easy way
out. Arno's suggestion performs certainly better than the IN clause.

I'll see whether I can change the way the SQL code is generated.

I think the reason that the IN clause pops up now and then as a
performance issue is I believe the optimizer of MS SQL Server does use
indexes when performing the IN clause in the following way

where table1.pk1 in (select table2.fk1 ...)

So you get into the habit of presuming that it would work across
different types of db's.

Thanks again.
Cihan


--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

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