db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Re: Performance Tuning Problem
Date Mon, 09 Apr 2007 21:52:01 GMT
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


Mime
View raw message