Hi,

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

With a [LEFT | RIGHT] OUTER JOIN you will force the way how tables are
fetched, because the outer table needs always to be processed as first one.
For Example with "SELECT * FROM A LEFT JOIN B ON (<whatever>)" table A is
always the first one and B depends on A together with the ON-clause. If you
don't want a INNER JOIN you should use a INNER JOIN, because know the
optimizer can decide in which order those relations needs to be processed.

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

The optimizer is clever enough to figure this out with INNER JOINs.

> 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?).

This already happens.

2 indexes will be used in this example:
SELECT * FROM
RDB$RELATIONS r
JOIN RDB$RELATION_FIELDS rf ON (1 = 1)
WHERE
r.RDB$RELATION_NAME = 'RDB$DATABASE' and
rf.RDB$RELATION_NAME = 'RDB$DATABASE'

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

This already happens, if there's no result (per record/row) on a relation
the "deeper" relation(s) that depend on the previous relation will not be
executed.

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

Yes, those plans are there. If i'm not wrong it is on Dmitry's list, but
this will certainly be after Firebird 2.0

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81