db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Query execution never ends
Date Sat, 20 Mar 2010 16:16:29 GMT
Witold Szczerba wrote:
> 2010/3/19 Bryan Pendleton <bpendleton.derby@gmail.com>:
>>> SELECT count(*)
>>> FROM contract co
>>> LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND
>>> insured.id=co.insured_id
>> In this query, how does the result (the returned count) change when you
>> change LEFT JOIN to JOIN?
> The numbers are almost always the same, it is possible for some
> contracts not to have insured subjects temporally.
> Right now the results are: 17667 (left join) and 17666 (inner join).

Then it doesn't sound like the performance problem is due to a different
amount of data in the outer join case.

Perhaps it is that the presence of the outer join is preventing some
optimization and thus Derby is choosing an inferior query plan.

Have you compared the query plans of the two queries in the empty table case?

Also, have you experimented with updating the statistics or compressing
the tables in your full-size database? If the statistics are out of date,
Derby has been known to choose a bad query execution plan.



View raw message