Hi,

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

>

The first thing that I discontinue in interbase was the INNER JOIN
and adopted LEFT JOIN because A LOT of times inner joins
took "forever" and LEFT JOIN gave me the result pretty fast (just
take care of nulls when necessary). I just thought "ok, IB just work
this way. Let's forget MSACCESS and do IB".

The same example from the begining (LEFT JOIN 1.4 sec)
******************************************************
SELECT B.TB_INSPECT_ID
FROM TB_INSPECT_GROUP A LEFT JOIN TB_INSPECT B
ON (B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID)
WHERE
A.DEST_LOT_ID IS NULL
AND A.SO_DET_ID IS NOT NULL
AND B.WRITE_UP_DET_ID IS NULL
AND B.STATUS IN ('PASSED','USE AS IS')


PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX (RDB$FOREIGN112)):
PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX (RDB$FOREIGN112))

Adapted plan:
PLAN JOIN (A INDEX
(FK_INSPECT_GROUP_LOT_DESTFK_INSPECT_GROUP$FOREIGN112))
takes 1.43sec


********************
Do you think there's a way to write the original query in FB (only
one select statement) and be optimized as in the SP?

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

Modified example using (INNER JOIN cancel after 1+ minutes)
******************************************************

SELECT B.TB_INSPECT_ID
FROM TB_INSPECT_GROUP A inner JOIN TB_INSPECT B
ON (B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID)
WHERE
A.DEST_LOT_ID IS NULL
AND A.SO_DET_ID IS NOT NULL
AND B.WRITE_UP_DET_ID IS NULL
AND B.STATUS IN ('PASSED','USE AS IS')

PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX
(IDX_TB_INSPECT_WUP_DET,RDB$FOREIGN112,IDX_TB_INSPECT_STATUS,IDX_TB_I
NSPECT_STATUS)):
PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX
(IDX_TB_INSPECT_WUP_DET,RDB$FOREIGN112,IDX_TB_INSPECT_STATUS,IDX_TB_I
NSPECT_STATUS))

Adapted plan:
PLAN JOIN (A INDEX (FK_INSPECT_GROUP_LOT_DEST),B INDEX
(IDX_TB_INSPECT_WUP_DET,RDB$FOREIGN112,IDX_TB_INSPECT_STATUS,IDX_TB_I
NSPECT_STATUS))
takes forever (I mean 1 min+)

Modified example using (INNER JOIN cancel when 1+ minutes)
changed to B INNER JOIN A. Same plan. Same time?
******************************************************
Exactly the same plan, same execution.

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

It's not about using the most indexes. It's about performance.-See
the PLANS above, more indexes in the INNER JOIN but extremely slow
-Left join is Ok, but it still can do better.

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

See the original example. It should enter only 237 times to the last
tables, but it goes thousands.

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

I'm not really sure if those optimizations "already happens" at
least (I think) not in my databases. May be you already do it, but a
big "real" databases already working could help to test the engine.

Don't missunderstand me, I wanted to report this issue
(optimization) because I haven't seen much real complains about this.

Hope This Helps.

Juan Jose.