I found an interesting preformace problem that I'd welcome some help in
inderstanding.
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?
Tim
