Hi,

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

Well, it shouldn't work that way :-)

> 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))
> takes 1.43sec

Are there 2 PLANs returned by this query or is it just a copy-paste mistake?
Assuming 1 PLAN is returned, 1 index on A and 1 index on B is used.
I've to guess on which fields, may be you could post more metadata about the
indexes used.

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

A Query PLAN is based on estimated information (index statistics and
table-information).
You can force a query by given it explicit the PLAN, but my advice is to
forget that.

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

The order is choosen right by the engine (A, B) in this case.
(Although you've a IS NULL on a field in A.
IS NULL and STARTING WITH are not used by the engine to decide the JOIN
order)
But on B there are many more indexes used then in your LEFT JOIN query.
On which engine did you run this query? 1.5.1?
Did you run "SET STATISTICS INDEX <index-name>" for every index?
I would like to see the statistics for the indexes involved.

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

Should indeed be the same PLAN.

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

In your case all those indexes are the problem, to many indexes are used.
May be you've defined to much indexes? or defined wrong indexes.

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

If you've the possibilty to test with FB1.5 (not 1.5.1) please let me know
what that's reporting.

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

I use also production-databases to test with, but how more the better of
course. The optimizer is already better than it was, but certainly not
perfect. There are still many issues that can be worked on and that will be
done.

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

This is the best way, asking fb-support and when it's a problem by the
engine it will be reported to fb-devel. We just need to find out what is
causing those troubles for you.

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