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

>
> --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> >
> > I assume that you have taken into account possible network
> > configuration errors that may slow it down. Are the databases
> > identical in contents on both servers? If not the statistics may be
> > suitably different for the optimiser to attempt another path on the
> > windows server.
> >
> > 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.
> >
> > Obviously, you will need to check the query plans on both servers. If
> > they are identical, then look elsewhere for your problem. Otherwise
> > you may need to coax the plan on the windows box by disabling the
> > possibility of an index so it chooses the way it did on the Mac. This
> > will not affect the Mac performance.
> >
> > If you get stuck, post your query, query plans on both OS, and
> > relating table structures and indices.
> >
> > Adam
>
> 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)

Looking at your query I find this strange. When I try the same thing
in several similar master detail type relationships on Windows, both
queries use the exact same plan regardless to whether you put the real
condition on the FK in RES or the PK of BASER.

I would expect both queries to come up with the following plan on both
platforms (substituting the PK and FK index names obviously)

PLAN JOIN (BR INDEX (PK_BASER),R INDEX (FK_RES_BASER))

Of course between Firebird versions, the optimiser is improved
(hopefully) or optimisations that cause problems may be undone, so if
you are not using the exact same version on both, then all bets are off.

> I'm using IBExpert and it shows no 'Plan' or 'Adapted Plan' (for
> either query/either platform), which seems strange since most other
> queries do. Does this mean the query has not been analysed(??) or
> optimised?

Unless you specifically provide a plan, one will be calculated. Try
using iSQL. To enable Type

SET PLAN;

> For now we are using the version info on the JDBC connection to tell
> us what platform the database is on (Mac contains 'UP', Windows 'WI'
> and Linux 'LI').

It might get around the problem in this case, but it wont help in the
long run. Is there anything you are not telling us about the query?
Things like order by, group by, joining to SP or views with aggregates
can influence behaviour significantly.

Adam