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,
-Rick
Regards,
-Rick
