db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kane, David" <David_K...@sra.com>
Subject RE: Performance Tuning Problem
Date Mon, 09 Apr 2007 20:57:57 GMT
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:bpendleton@amberpoint.com] 
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


Mime
View raw message