db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Performance issue of derby using JDBC
Date Mon, 15 Dec 2008 19:35:26 GMT
Harshad wrote:
> Bryan Pendleton wrote:
>> Harshad wrote:
>>>   "select name,version,release,time from PKG where PKG.id in (select id
>>>   from PROVIDES where name = ?)",
>> What happens if you run this statement instead:
>> select pkg.name,pkg.version,pkg.release,pkg.time from pkg, provides
>>         where pkg.id = provides.id and provides.name = ?
> Whew! That solves it! No need for numbers, they are sub milli-second now!
> Can't the SQL optimiser do this automatically? Is this a derby specific problem?  (Sorry
for amateurish questions; this is my first real db application)
> I ask because I have another application where I programmatically generate queries, and
the former type of query is easier to generate than the one you suggested. I always assumed
that the SQL optimiser will "flatten" the query...
> Thanks,
> Harshad
Hi Harshad,

Depending on your data, the two queries could return different results, 
so Bryan's faster query is not a transformation which Derby would 
perform automatically. Nevertheless, it looks to me as though your 
original query almost qualifies for the EXISTS transformation documented 
in the Derby Tuning Guide. For more information, please see the 
"Subquery processing and transformations" section of the Derby Tuning 
Guide: http://db.apache.org/derby/docs/10.4/tuning/

Hope this helps,


View raw message