| Subject | Re: Query Optimisation problems on different platforms |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2006-02-21T07:30:10Z |
--- In firebird-support@yahoogroups.com, "phil_hhn" wrote:
but something is wrong if it refuses to show you the plan.
twice in a row (once could be garbage collection, twice could be a
long-running query killing the performance for everybody - the latter
is highly unlikely if you executed the quick query in between two
execution of the slow one).
stupid if every row satisfies the criteria, but with your strange
problem maybe statistics not up to date could do something like this?
are identical. To ascertain there is a difference between platforms,
you have to restore a transportable backup on both platforms using the
same version of Firebird and see that there is a difference in the
plan (and execution) of the query before doing anything else to the
newly restored databases. I know that different contents of a database
may influence the plan, and that means (although I've never heard of
such a case) that in theory the addition, removal or modification of
one record may be enough for the optimizer to make us puzzled!
Set
> We will have to try with iSQL to see if we can view the plan!I've never used IB Expert (I think you wrote that was what you used),
but something is wrong if it refuses to show you the plan.
> There is not too much else we thought would be worth mentioning; theI assume you got consistent slow results, not just tried it once or
> 'real' tables in our system have a lot more columns and there are
> joins to other tables. The databases & queries are the same for both
> Mac and Windows (at least until we started pulling this all apart!).
twice in a row (once could be garbage collection, twice could be a
long-running query killing the performance for everybody - the latter
is highly unlikely if you executed the quick query in between two
execution of the slow one).
> Another thing we noticed - we originally had:'=1' can use an index, '<>2' cannot. Using such an index is obviously
> "where BR.BR_SN in (1,5,9) and RECORDSTATUS = 1"
> (Every single record has recordstatus set to 1.)
> Again, this was part of the query that was ok on Windows, but on the
> Mac if we changed the last part to "RECORDSTATUS <> 2", it ran fast,
> just as if we had changed "BR.BR_SN in..." to "R.BR_SN in..."!!
stupid if every row satisfies the criteria, but with your strange
problem maybe statistics not up to date could do something like this?
> However that seemed to not make an improvement if other parts of theDifference between platforms can only be shown if all other variables
> query changed.
>
> This really does seem to be a problem in the automatic optimisation,
> we are mainly surprised that there'd be any difference at all
> between platforms.
are identical. To ascertain there is a difference between platforms,
you have to restore a transportable backup on both platforms using the
same version of Firebird and see that there is a difference in the
plan (and execution) of the query before doing anything else to the
newly restored databases. I know that different contents of a database
may influence the plan, and that means (although I've never heard of
such a case) that in theory the addition, removal or modification of
one record may be enough for the optimizer to make us puzzled!
Set