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? thanks, bryan
