db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Witold Szczerba <pljosh.m...@gmail.com>
Subject Re: Query execution never ends
Date Sat, 20 Mar 2010 23:36:18 GMT
2010/3/20 Bryan Pendleton <bpendleton.derby@gmail.com>:
> 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.
>
> thanks,
>
> bryan
>
>

Finally, I have both query plans, one for:
SELECT count(*)
FROM contract co
JOIN user_contract uc ON co.id=uc.contract_id
JOIN ContractSubject insured ON insured.contract_id=co.id AND
insured.id=co.insured_id
(Executed successfully in 0.959 s.)
http://witoldsz.strefa.pl/queryplan-1.txt

and second:
SELECT count(*)
FROM contract co
JOIN user_contract uc ON co.id=uc.contract_id
LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND
insured.id=co.insured_id
(Executed successfully in 2,098.677 s.)
http://witoldsz.strefa.pl/queryplan-2.txt

The problem is that I look at those plans - it is like black magic to
me, maybe you can see something?

I issued SYSCS_COMPRESS_TABLE for all tables involved, but have not
tried SYSCS_UPDATE_STATISTICS as this procedure does not exist in
Derby 10.4. I will upgrade to 10.5 and try that.

Thanks,
Witold Szczerba

Mime
View raw message