"arindam.bhattacharjee" <[email protected]> writes:
> Hello, > > I would like my query below to return within 100 millisecs. Please help me, > and the values for the IN clause comes from outside hence cannot really > change the IN clause to a join on an existing table. Hi Arindam, Does the query run faster if you compress all the tables involved, or if you drop and recreate all the indexes? If so, it is likely that the index cardinality statistics are out of date, which may make the optimizer pick a bad execution plan. Currently, index cardinality statistics are only updated at index creation time, when tables are compressed, and when columns are dropped. A more automatic solution is being worked on. For more details, see: https://issues.apache.org/jira/browse/DERBY-269 https://issues.apache.org/jira/browse/DERBY-3788 http://db.apache.org/derby/docs/10.4/tuning/ctunstats18908.html You may be experiencing some other problem, but this is a problem that keeps coming up, so I think it's worth checking. Hope this helps, -- Knut Anders
