Tim, First, why are you using a floating point or a double inside of the database? Try using numeric data types like DECIMAL. (Unless you mean that you're using a DECIMAL inside derby but are storing it in your java app as a double...)
Putting an index on a floating point column? Ick! Then there's one other question... you have ~270K rows where the indexed value is between 300 and 320. That's 270K where the value is one of 20 values. Just on average each index has ~10K rows associated with it. So an index isn't going to help out that much. Does that make sense? HTH -Mike > -----Original Message----- > From: news [mailto:[email protected]] On Behalf Of Tim Dudgeon > Sent: Wednesday, March 25, 2009 2:30 PM > To: [email protected] > Subject: Index perfomance > > 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
