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

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.

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?
I currently have IBExpert 'Personal Edition' in front of me so it
doesn't give me access to the 'Plan Analyzer' (I could check this
later in the week).

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

Cheers