db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harshad <harshad...@gmail.com>
Subject Re: Performance issue of derby using JDBC
Date Wed, 17 Dec 2008 05:20:12 GMT
Rick Hillegas wrote:
> 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. 

Oh yes! Although my data ensures that the subquery would return distinct results, Derby wouldn't
know about it. Is there a way to say that the combination of two columns is unique?

When I tried to specify DISTINCT explicitly like this:
select name,version,release,time from PKG where PKG.id in (select DISTINCT PROVIDES.id from
PROVIDES where (PROVIDES.name = ?))

...I didn't get any change in the performance figures. That is probably because of the "DISTINCT
elimination in IN sub-queries" mentioned in "Tuning derby".

I would have thought that the DISTINCT is not redundant and actually allows for the transformation
into a normal join.

> Nevertheless, it looks to me as though your
> original query almost qualifies for the EXISTS transformation documented
> in the Derby Tuning Guide. 

Why do you say "almost qualifies"; where is it not qualifying? And, will the EXISTS transformation
speed up the query as much as the transformation to a normal join?

Thanks to all who have responded on this thread.

I have learnt not to trust the execution time of Statement.executeQuery(). It looks like the
actual table access is made lazily (as and when the result set is accessed).

View raw message