Hi,

> When reading the thread i'm afraid Juan hit a bug introduced in

FB1.5.1

> This caused unneeded fetches for relations which depends on

relations with a

> OR condition bound to it and no index could be used by that OR

condition.

> This will be fixed in the next Firebird releases.
>

This query is present since the beginning (FB 1.0). When I upgraded
to 1.5.1, I didn't see much improvement. But the query is getting
slower as database is growing up.

I'm still wondering how the optimizer works? - and, I remember to
read a document explaining it.

In the very beginning I wrote queries just to get the right result
set.
But, soon, I noted that:

A LEFT JOIN B
ON A.ID=B.ID
WHERE B.X=VALUE

and

B LEFT JOIN A
ON A.ID=B.ID
WHERE B.X=VALUE

Although, both returned exactly the same retult set(I know it can
vary depending an nulls on each table - but, suppose this is not the
case!) the second one made the very big difference and started
writing queries putting the tables that had a where clause in the
beginning. This make the queries FASTER.

Because I have a small "query builder" for my application I has to
teach super end-users to start from the table that contained the
WHERE clause.

Now, that my queries are "faster", I note that they could be FASTEST
by not visiting unnecesarily some table. unfortunately, I can't
afford this by writing a single SQL statement, but, writing a stored
procedure.

I'm still thinking the that the optimizer can do more for us by:

-Applying the WHERE conditions to the individual tables before
joining them. (Any technical difficulty?).

If I have
A JOIN B
ON A.X=B.X
JOIN C
ON A.Y=C.Y
JOIN D
ON C.Z=D.Z

Could it be (for not visiting unnecesarily)?:
[[[A JOIN B ON A.X=B.X] R1
JOIN C ON R1.Y=C.Y] R2
JOIN D ON R2.Z=D.Z]

Taking advantage of your attention:
I discontinue all my IN Clauses (select inside) because it executes
once for each record. I thing that this syntax has a huge potencial
and simplicity but it's not efficient
WHERE A IN (SELECT ID ...).

Is there a technical obstacle to execute the in clause ONLY ONCE and
a) compare each record against the local copy or
b) replace the statement with A=1 OR A=9 OR A=14 OR ... etc.

Do you have plans to get this kind of optimizations for future
versions of FireBird?

Thanks

Juan Jose

> Regards,
> Arno Brinkman
> ABVisie