db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: URGENT!!! JDBC SQL query taking long time for large IN clause
Date Wed, 08 Apr 2009 14:36:22 GMT
> 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?



View raw message