Folks, Here are the indexes that are being reported on the ASSOCIATION table:
INDEX_QUALIFIER INDEX_NAME ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC NON_UNIQUE TYPE CARDINALITY PAGES FILTER_CONDITION SQL070329021801090 1 ID A false 3 <null> <null> <null> A1 1 TERM_ID A true 3 <null> <null> <null> A2 1 GENE_PRODUCT_ID A true 3 <null> <null> <null> A3 1 TERM_ID A true 3 <null> <null> <null> A3 2 GENE_PRODUCT_ID A true 3 <null> <null> <null> A4 1 ID A true 3 <null> <null> <null> A4 2 TERM_ID A true 3 <null> <null> <null> A4 3 GENE_PRODUCT_ID A true 3 <null> <null> <null> I think all of the columns referenced for this table in the queries, ID, TERM_ID and GENE_PRODUCT_ID have individual indexes, and in some cases additional indexes. 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? Sincerely, David Kane -----Original Message----- From: Bryan Pendleton [mailto:[EMAIL PROTECTED] Sent: Monday, April 09, 2007 3:50 PM To: Derby Discussion Subject: Re: Performance Tuning Problem Do you have indexes on the important columns of the ASSOCIATION table? From a quick peek at your query plan, this seems to be killing you: Right result set: Table Scan ResultSet for ASSOCIATION ... scan information: Bit set of columns fetched={1, 2} Number of columns fetched=2 Number of pages visited=55790 Number of rows qualified=14 Number of rows visited=10488516 Scan type=heap It's scanning through 10 million rows in the ASSOCIATION table... thanks, bryan
