Kane, David wrote:
Here are the indexes that are being reported on the ASSOCIATION table:
[ snip index display ]
Hate to be a nuisance, but can you post the DDL for the tables in the query? I
find it easier to make sense of the DDL than to parse the index table (maybe
it's just me). You can get that by using "dblook":
http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html
Ex.
java org.apache.derby.tools.dblook -d jdbc:derby:mydb -t association
Might be nice to get the DDL for all of the tables in the query, not just
"association".
Even if there was an index problem here, would that explain why it seems
to be taking so long to loop through the ResultSet object?
Can you say how many rows the two queries that you posted return?
If you have a table with over 10 million rows in it (which you do) then any
index problem that causes the optimizer to choose a table scan (which it does,
according to the program output) could definitely cause a slowdown. Especially
if that 10 million row table is then joined with another table that has over a
million rows of its own (which is, I think, what your query is doing). So yes,
an index problem could definitely explain the behavior you're seeing.
Did you by chance create the indexes before you inserted the data? If so, there
are known problems with doing so: see esp. DERBY-269. Given that, it's
generally a good idea to populate the tables first and *then* create the indexes
afterward. Doing so provides Derby with better statistics, which in turn leads
to better cost estimates and (hopefully) better plans.
I also noticed that the "moreComplexQueryResults" query includes several IN
clauses. Depending on the indexes present, that query may be suffering from
DERBY-47, for which some changes have already been made for the upcoming
release. See that Jira (https://issues.apache.org/jira/browse/DERBY-47) for
more details.
> I have put a copy of the derby database at ftp://<snip>
How large is this database zip file? Is it possible to post it to a Derby Jira
issue for ease of community access?
Army
