db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Index perfomance
Date Wed, 25 Mar 2009 19:29:34 GMT
I found an interesting preformace problem that I'd welcome some help in 

I have a table of 4,000,000 rows that has a DOUBLE column containing 
floating point numbers.
I run a query like this:

select pk_column from the_table where the_double_column > 300 and 
the_double_column < 320

271136 rows are returned.
I then go through the ResultSet and extract all the id_column values.
All of this is done using standard JDBC.

When I do this it takes 23 seconds, which I though was not unreasonable 
as a full table scan was involved and the table was pretty big.

But of course I thought an index would help, so I added an index to the 
the_double_column and repeated the process. It took 720 seconds, 31x 
slower! I thought this was strange, but thought it might be because I 
was using standard settings and the 4MB page cache was much too small to 
hold the index.

So I increased the page cache size (derby.storage.pageCacheSize 
property) to 10x the size (10,000) and repeated the process. There was 
only a very minor improvement in speed.

In all cases the memory usage, as reported by:
Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()
really no differnt, and the used memory was much less that the maximum 
available specified by the -Xmx setting.

Any ideas what to do?


View raw message