Re: Query execution never ends

2010/3/21 Witold Szczerba <[EMAIL PROTECTED]>:
> 2010/3/20 Bryan Pendleton <[EMAIL PROTECTED]>:
>> Witold Szczerba wrote:
>>>
>>> 2010/3/19 Bryan Pendleton <[EMAIL PROTECTED]>:
>>>>>
>>>>> 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
>

OK,
I have downloaded Derby 10.5.3.0, upgraded the database, compressed
tables and updated theirs statistics, invoked both queries and it did
not help. Here are the query plans:
http://witoldsz.strefa.pl/queryplan-1b.txt
http://witoldsz.strefa.pl/queryplan-2b.txt

By the way: is there any tool for those query plans to be better
readable? They are extremely verbose - 360 lines for one simple query
- those query plans are overwhelming. Maybe some grep/sed/awk scripts
at least?

Regards,
Witold Szczerba