Hi, Sorry for top posting. What you're looking for is an index. :-)
Ok, I know you know about indexes on individual columns but you can create an index on multiple columns too! It's the same statement that you use to create your other indexes but you can identify multiple columns where the order of the column is the order of precedence. (foo, bar) would first index on foo then on bar. I'm not sure what online references exist, or what is today a good book on databases. I don't know if they have anything from CJ Date ??? in print these days. (Sorry, its either alzhimers, or a lack of caffeine, or both.) Date was the golden reference for relational databases when I was in school some 20+ years ago. You can create as many indexes on a table as you want, provided that the names of the indexes are unique. One major caveat... Too many indexes may confuse the optimizer and when your query is processed, the engine may choose a different index. HTH -Mike > -----Original Message----- > From: news [mailto:n...@ger.gmane.org] On Behalf Of Harshad > Sent: Tuesday, December 16, 2008 11:20 PM > To: derby-user@db.apache.org > Subject: Re: Performance issue of derby using JDBC > > 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).
