Hi again Juan!

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

Forget MSAccess and do IB is a good idea, but to forget inner joins
and do left joins is not. Anyway, does this mean that you could get
the result you want using inner joins? If so, I'd be much more
comfortable with your query.

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

It should at least be possible to optimize, then we will find out in
the end whether it is identical to the SP, slower or faster (I don't
think you can make a straigth join much faster than an optimized
stored procedure, but I'm not yet convinced your SP is optimized).

> 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_INSPECT_STATUS)):

And this reveals the problem. One of the weaknesses of Firebird (in my
opinion) is that it tries to use too many indexes. Combine that with
someone who creates too many indexes and Firebird gets unbearably
slow. Having an index on STATUS is normally not a good choice, unless
the selects based on STATUS normally obtain those records which has an
unusual status (i.e. a search on STATUS = '1. prize' in a lottery
could benefit from an index).

Change your query to

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') OR 2=0)

This should prevent IDX_TB_INSPECT_WUP_DET and IDX_TB_INSPECT_STATUS
from being used with this query.

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

Report back the results after changing your SQL to use inner joins and
the (... OR 2=0) above.

> 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 misunderstand me, I wanted to report this issue (optimization)
> because I haven't seen much real complains about this.

I think your issue largely is due to the assumption that left joins
are better than inner joins combined with the creation of too many
indexes.

Set