> Lastly, are you now using Derby 10.4 for all of this work, or are
> you continuing to use 10.1?
[Arindam] I will respond to the rest of your questions ASAP. The answer to the above question is : 10.4. I am using 10.4 now and it is significantly better than 10.1 both during insertion and during query.
problem - thanks to Knut for indicating that as a possibility. When I compress the tables - the quries start performing a LOT faster!
Good, I'm glad to hear that you've made some good progress.
Do you get a different query plan now? Or is it just that the table
access is more efficient?
Can you capture the runtime statistics information for the 30 second
query before you compressed the tables, and for the 1.1 second query
after you compressed the table, and compare them?
You mention that you've run this query on other databases, and they
are much faster. Can you make any observations about why that might
be, and what they may be doing differently? For example, can you see
if the other database is using a different query plan and let us know
the general information about what that query plan is?
Also, you mention that the query runs quite well with 100 or 500 elements
in the IN clause, but falls down with 1000 elements in the IN clause.
Again, I'd be interested to know how the runtime statistics information
compares between those two cases. Is it that we using a substantially
different query plan for the larger query? Or is there some other behavior?
Lastly, are you now using Derby 10.4 for all of this work, or are
you continuing to use 10.1?