>This concerns SQL generated by an O/R mapper, so please don't tell me how to improve
>the SQL (I'm trying to bring it to the dev team's attention).
>
>Anyway, the SQL is as follows:
>
>SELECT Company.Id
>FROM Company
>WHERE EXISTS (
> SELECT Id
> FROM Country
> WHERE Company.Country = Country.Id
> AND Country.Code = ?
> )
>
>I think the semantics are pretty obvious. Of course better SQL with equivalent sematics
>would be:
>
>SELECT Company.Id
>FROM Company
>JOIN Country on Country.Id = Company.Country WHERE Country.Code = ?
>
>The latter SQL will let FB use an index on Company.Country, but the former SQL will always
>cause FB to scan the entire Company table. If this table is large and the index has good
>selectivity, this is a huge performance hit.
>
>So, is there any way I can get FB to use the index for the first query?

Dmitry already answered what I would expect to be the obvious answer, when the O/R mapper choose to generate simple SQL without caring about performance, you can't expect the database to transform it into something considerably better (although Firebird in many cases could change IN(<subselect>) to EXISTS(<subselect>) if your O/R mapper had been really bad). Of course it is thinkable to have an optimizer really optimize such code, but EXISTS can be infinitely more complex than your example and there's no rule that says that EXISTS should not involve joins or tables bigger than the one you're selecting from. I'd even expect the normal case to have a selective WHERE clause where EXISTS was only one part, e.g. a query looking more like:

SELECT Company.Id
FROM Company
WHERE Enterprise = 'Volvo' AND
EXISTS (
SELECT Id
FROM Country
WHERE Company.Country = Country.Id
AND Country.Code = 'SE'
)

(i.e. trying to find all companies belonging to Volvo that is located in Sweden)

Not even considering the time or complexity implementing the optimization you're looking for, I'd guess the additional time needed for preparing statements versus the few number of cases where the optimization would be beneficial, to make it questionable whether the change would be desirable. Though, of course, computer speed and storage volume increase year by year, so prepare time become less and optimization more important.

Set