Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of pljosh.mail@gmail.com
 designates 209.85.218.225 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type;
        b=v/UE7szwoxxIlUvOOA+TL0LRt04WmwH1qgGbPYEz/f1wKFF135jSgaxIEHYPldlQ44
         e0dmRPItQcVvRt+F/OlnVSf2S8dsTmpa3qZGa3zCE+Mc3vu9QUCNr9xnPImQmhy5/NSn
         vNpGf4FcbHsYkbqClevJFwZWYr7g3NX8BmZlw=
MIME-Version: 1.0
In-Reply-To: <e0a08d801003201636vac60a97m21bf9a781d9f1058@mail.gmail.com>
References: <e0a08d801003190619l3d3181pa55ca79145b7810d@mail.gmail.com>
	 <4BA3826D.2060203@gmail.com>
	 <e0a08d801003190737r608cdb81s11f7b58f485d78d7@mail.gmail.com>
	 <4BA4F4DD.6060702@gmail.com>
	 <e0a08d801003201636vac60a97m21bf9a781d9f1058@mail.gmail.com>
Date: Mon, 22 Mar 2010 01:30:25 +0100
Message-ID: <e0a08d801003211730i7e31dd09s71c300ba413fa37f@mail.gmail.com>
Subject: Re: Query execution never ends
From: Witold Szczerba <pljosh.mail@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=UTF-8

2010/3/21 Witold Szczerba <pljosh.mail@gmail.com>:
> 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
>

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