--- In firebird-support@yahoogroups.com, "phil_hhn" wrote:

> --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> > The optimiser gets it right 99% of the time, and the other 1% you
> > need to encourage it to not use a particular index by adding 0 to
> > the field in the join etc.
>
> The only difference in the databases is that we started with a Mac
> database, so we had to make a transportable backup so we could
> restore it onto Windows.
>
> Here is a simplified version of the two tables which are at the
> center of our SQL. Basically for each object stored in this
> database, it is stored as one row in the BASER table and one 'main'
> row in the RES table, where R.BR_SN is a fk to the BASER.BR_SN.
> There can also be multiple RES rows attached to the 'main' RES.
>
> create table BASER
> (
> BR_SN INTEGER not null,
> DESCRIPTION CHAR(70) ,
> RECORDSTATUS INTEGER not null,
> constraint PK_BR primary key (BR_SN)
> );
>
> create table RES
> (
> R_SN INTEGER not null,
> BR_SN INTEGER ,
> OWNER_R_SN INTEGER ,
> EDITION VARCHAR(80) ,
> RECORDSTATUS INTEGER not null,
> constraint PK_R primary key (R_SN)
> );
>
> Our original query (worked ok under windows) was like:
>
> select BR.DESCRIPTION, R.EDITION from BASER BR
> join RES R on R.BR_SN = BR.BR_SN
> where BR.BR_SN in (1,5,9)
>
> This performed very badly on the Mac (20x-30x slower). But we
> changed the where clause:
>
> where R.BR_SN in (1,5,9)
>
> The Mac now performs at a comparable speed (to the original query on
> Windows). Note though that this new query runs badly on Windows.
> So it seems we have to use different queries on each platform.

These queries are so simple that they should belong in the 99% of
cases that the optimizer gets right. The only case I can think of
where the optimizer may get confused (at least used to), is if you
have two "indexes" on either table that has BR_SN as the first field.
I put "indexes" in double quotes, since I here mean "indexes" in a
wider sense including both keys and indexes.

You could write

select BR.DESCRIPTION, R.EDITION from BASER BR
join RES R on R.BR_SN = BR.BR_SN+0
where BR.BR_SN in (1,5,9)

or

select BR.DESCRIPTION, R.EDITION from BASER BR
join RES R on R.BR_SN+0 = BR.BR_SN
where R.BR_SN in (1,5,9)

to get a queries that I expect to be brilliant on both platforms, but
something fishy is happening and excepting the possible reason above I
have no idea what the problem could be. In my experience (only
Windows), it doesn't matter whether you use 'where BR.BR_SN in
(1,5,9)' or 'where R.BR_SN in (1,5,9)' as long as they are linked with
an [inner] join clause. I believe the optimizer choose the most
appropriate anyway and internally 'modify' the query if necessary.
Note that I'm not talking about the source code of Firebird (I don't
know that), just my experience with writing queries.

HTH,
Set