| Subject | Re: Query Optimization |
|---|---|
| Author | Adam |
| Post date | 2006-05-09T03:09:55Z |
--- In firebird-support@yahoogroups.com, "g.ingram" <geoff@...> wrote:
world, the optimiser would recognise all of its possible options,
know the exact cost of each option, and do so immediately. In the
real world, optimisers have not thought of each possible option, do
not have a 100% accurate cost estimation procedure, and have a
limited amount of time to come up with the plan.
Firebird usually does a reasonable job. Sometimes it does not. The
usual culprit is that the information about an index becomes out of
date. In other words, one of the following problems occurs
1) An index is perceived to be more costly than it actually is
2) An index is perceived to be less costly than it actually is.
In the first case, Firebird may avoid using a particular plan because
it involves using a particular index which Firebird had overestimated
the cost on.
In the second case, Firebird may choose a particular plan because it
seemed to be a good idea at the time, but then reality strikes.
One of the advantages of DBMS like Firebird is that people who have
spent much time considering the different factors that influence the
speed of an operation, and those people are better equiped to advise
us on the fastest way to run the query. Sometimes, usually due to
incorrect statistics information, things head south (garbage in,
garbage out). But on the counter side, as the optimiser improves, it
comes up with faster plans that we have not considered. So it is, on
balance, a good thing to have. You will find several cases
(theoretical and also in practice) where a query that 'ran fine'
under Firebird 1.5, goes even faster under Firebird 2. 'ran fine' is
of course a subjective statement that simply means it runs as fast as
it has to run to not be a drain on resources and not cause any
complaints.
Most if not all admin tools will tell you the query plan used. In
iSQL, simply type 'SET PLAN'. Compare the two plans, and you will see
the differences.
Run SET STATISTICS for each of the indices involved and see if that
fixes things. If not, then the problem is that, although Firebird has
an accurate statistics, the optimiser got it wrong.
A useful trick is to use +0 for integer/timestamp based fields and
||'' for string fields when you want to prevent an index from being
used. Some other DBMS use 'hints' for this case.
Sometimes, the culprit is a foreign key index which has poor
selectivity.
For example, 99% of employees of a company may have been born in a
single country.
Select e.Name
from Employee e
join Country c on (e.CountryOfBirthID = c.ID)
where c.Name = :CountryName
Firebird may choose
PLAN JOIN (C NATURAL,E INDEX (FK_COUNTRY_1))
When the following plan may be better.
PLAN JOIN (E NATURAL,C INDEX (PK_COUNTRY))
The following query would achieve this:
Select e.Name
from Employee e
join Country c on (e.CountryOfBirthID+0 = c.ID)
where c.Name = :CountryName
shape may change, and this is a good thing, a sort of self
maintenance.
Craig Stuntz has written a few pages about IB, and a lot of it is
just as applicable to FB, so you can google for his work and have a
read.
If you have trouble adapting your original query to prevent the rogue
indices, then post their plans back to the list.
Adam
>following
> I have two tables which in six months of use have grown to the
> sizes:(over a
> EXAM - 96,258 records
> EXAM_ITEM - 23,228 records
>
> This query worked fine until today when it started bogging down
> minute to execute):NULL)
>
> select distinct
> EXAM_ITEM.EXAM_ID
> from
> EXAM_ITEM
> join
> EXAM on EXAM.EXAM_ID = EXAM_ITEM.EXAM_ID
> where
> (EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE = 'F')
> and
> (EXAM.REPORT_APPROVED_DATE is not NULL)
> and
> (
> (EXAM.PRI_INSURANCE_CODE <> '' and EXAM_ITEM.PRI_LAST_BILLED is
> orNULL)
> (EXAM.SEC_INSURANCE_CODE <> '' and EXAM_ITEM.SEC_LAST_BILLED is
> orNULL)
> (EXAM.TER_INSURANCE_CODE <> '' and EXAM_ITEM.TER_LAST_BILLED is
> )than 1
>
> I changed it to the following and performance was restored (less
> second execution):EXAM.PRI_INSURANCE_CODE <>
>
>
> select distinct
> EXAM_ITEM.EXAM_ID
> from
> EXAM_ITEM
> join
> EXAM on EXAM.EXAM_ID = EXAM_ITEM.EXAM_ID
> where
> (EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE = 'F')
> and
> (
> (EXAM.REPORT_APPROVED_DATE is not NULL and
> '' and EXAM_ITEM.PRI_LAST_BILLED is NULL)EXAM.SEC_INSURANCE_CODE <>
> or
> (EXAM.REPORT_APPROVED_DATE is not NULL and
> '' and EXAM_ITEM.SEC_LAST_BILLED is NULL)EXAM.TER_INSURANCE_CODE <>
> or
> (EXAM.REPORT_APPROVED_DATE is not NULL and
> '' and EXAM_ITEM.TER_LAST_BILLED is NULL)The second query used a faster way of doing things. In an ideal
> )
>
> Can anyone explain why the second query is faster?
world, the optimiser would recognise all of its possible options,
know the exact cost of each option, and do so immediately. In the
real world, optimisers have not thought of each possible option, do
not have a 100% accurate cost estimation procedure, and have a
limited amount of time to come up with the plan.
Firebird usually does a reasonable job. Sometimes it does not. The
usual culprit is that the information about an index becomes out of
date. In other words, one of the following problems occurs
1) An index is perceived to be more costly than it actually is
2) An index is perceived to be less costly than it actually is.
In the first case, Firebird may avoid using a particular plan because
it involves using a particular index which Firebird had overestimated
the cost on.
In the second case, Firebird may choose a particular plan because it
seemed to be a good idea at the time, but then reality strikes.
One of the advantages of DBMS like Firebird is that people who have
spent much time considering the different factors that influence the
speed of an operation, and those people are better equiped to advise
us on the fastest way to run the query. Sometimes, usually due to
incorrect statistics information, things head south (garbage in,
garbage out). But on the counter side, as the optimiser improves, it
comes up with faster plans that we have not considered. So it is, on
balance, a good thing to have. You will find several cases
(theoretical and also in practice) where a query that 'ran fine'
under Firebird 1.5, goes even faster under Firebird 2. 'ran fine' is
of course a subjective statement that simply means it runs as fast as
it has to run to not be a drain on resources and not cause any
complaints.
Most if not all admin tools will tell you the query plan used. In
iSQL, simply type 'SET PLAN'. Compare the two plans, and you will see
the differences.
Run SET STATISTICS for each of the indices involved and see if that
fixes things. If not, then the problem is that, although Firebird has
an accurate statistics, the optimiser got it wrong.
A useful trick is to use +0 for integer/timestamp based fields and
||'' for string fields when you want to prevent an index from being
used. Some other DBMS use 'hints' for this case.
Sometimes, the culprit is a foreign key index which has poor
selectivity.
For example, 99% of employees of a company may have been born in a
single country.
Select e.Name
from Employee e
join Country c on (e.CountryOfBirthID = c.ID)
where c.Name = :CountryName
Firebird may choose
PLAN JOIN (C NATURAL,E INDEX (FK_COUNTRY_1))
When the following plan may be better.
PLAN JOIN (E NATURAL,C INDEX (PK_COUNTRY))
The following query would achieve this:
Select e.Name
from Employee e
join Country c on (e.CountryOfBirthID+0 = c.ID)
where c.Name = :CountryName
> Are there any docsconcerned
> on Firebird internals (other than the source code [#-o] ) that would
> explain? Or maybe a general text on query optimization? I am
> that I will have to change the query again in six months time.Well the plan itself may change in 6 months time because the data
shape may change, and this is a good thing, a sort of self
maintenance.
Craig Stuntz has written a few pages about IB, and a lot of it is
just as applicable to FB, so you can google for his work and have a
read.
If you have trouble adapting your original query to prevent the rogue
indices, then post their plans back to the list.
Adam