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).
