Hi,

At August 10, 2006, 7:47 AM, yoav112003 wrote:

> Is there someway to control how the optimizer rewrites the queries
> (i.e custom plans) ? or at least get more information about the chosen
> optimization?

No. The way that I approach slow queries is basically that I look to
see if I can't rewrite it in another way that could make it faster.
And, if I can't and it's a query that would be executed frequently, I
will create a stored procedure that will simplify what Firebird has to
do to return the data.

Also in FB 2, it is possible to use "derived tables"(queries) in the
FROM clause. So, that might help to further improve performance for
this kind of query. And, maybe that will lead to further improvements
in the optimizer in future versions of FB(i.e.: instead of converting
IN (SELECT...) into EXISTS(SELECT... ), it might be able to analyze is
it would be better to convert to an INNER JOIN (SELECT...)). Because,
then the sub-query would only be executed once and not for every row.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)