db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: URGENT!!! JDBC SQL query taking long time for large IN clause
Date Tue, 07 Apr 2009 13:43:27 GMT
"arindam.bhattacharjee" <mr.arindam.bhattacharjee@gmail.com> 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

Mime
View raw message