| Subject | Re: [firebird-support] First query very slow |
|---|---|
| Author | Ann Harrison |
| Post date | 2013-02-05T17:45:52Z |
On Tue, Feb 5, 2013 at 2:39 AM, Josef Kokeš <j.kokes@...>wrote:
Firebird generally optimizes queries with joins by constructing a series
of nested-loop retrievals looking up records from each loop-level by key
values. Often the order of the loops matters. For example, if you want
to look up order information for orders last Tuesday from customers using
Visa credit cards that expire in 2015 who shipped their orders to Ohio, you
could be looking at the Orders table, the Customers table, and the
Destinations table. Assume that all three tables include a CustomerID and
that there's an index on the Date in the Orders table, on the CardType and
ExpiryDate in the Customers table, and on the State in the Destinations
table, the loops could be Customers -> Orders -> Destinations or
Destinations ->Customers -> Orders or Orders->Customers->Destinations - or
several others, even worse. The Firebird optimizer considers all the
possibilities and picks the one that will minimize the number of records
that must be read to resolve the query.
That process cannot happen with outer joins, unless the query compiler is
clever enough to recognize a NOT NULL on the right side of a left join and
turn it into an inner join. A left outer join that does not exclude nulls
in the right side must make the left side the outermost loop, followed by
the right side.
Maybe in your experience, you always choose the optimal join order and
sometimes the Firebird optimizer does not. It depends on statistics that
may not be up to date. Even then, the result should be equivalent to
inner joins, not faster. In theory.
Good luck,
Ann
[Non-text portions of this message have been removed]
>I can't argue with your experience, but can wonder if it generalizes.
>
> > 2) LEFT JOIN followed by referencing to a field in the table being NOT
> > NULL makes the LEFT JOIN in reality become a (inner) JOIN, sometimes
> > that's OK for optimization, but you haven't mentioned that being the
> > case here.
>
> Maybe my queries are wrong, but my experience has been that LEFT JOIN +
> IS NOT NULL is very often far faster than an (INNER) JOIN.
>
>
Firebird generally optimizes queries with joins by constructing a series
of nested-loop retrievals looking up records from each loop-level by key
values. Often the order of the loops matters. For example, if you want
to look up order information for orders last Tuesday from customers using
Visa credit cards that expire in 2015 who shipped their orders to Ohio, you
could be looking at the Orders table, the Customers table, and the
Destinations table. Assume that all three tables include a CustomerID and
that there's an index on the Date in the Orders table, on the CardType and
ExpiryDate in the Customers table, and on the State in the Destinations
table, the loops could be Customers -> Orders -> Destinations or
Destinations ->Customers -> Orders or Orders->Customers->Destinations - or
several others, even worse. The Firebird optimizer considers all the
possibilities and picks the one that will minimize the number of records
that must be read to resolve the query.
That process cannot happen with outer joins, unless the query compiler is
clever enough to recognize a NOT NULL on the right side of a left join and
turn it into an inner join. A left outer join that does not exclude nulls
in the right side must make the left side the outermost loop, followed by
the right side.
Maybe in your experience, you always choose the optimal join order and
sometimes the Firebird optimizer does not. It depends on statistics that
may not be up to date. Even then, the result should be equivalent to
inner joins, not faster. In theory.
Good luck,
Ann
[Non-text portions of this message have been removed]