db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Index perfomance
Date Thu, 26 Mar 2009 12:37:59 GMT
Thanks for the comments. Here is some more info.
I attach the DDL for the table concerned, the simple test program I use 
and the execution strategy with and without an index.

Some additional points:

1. the query returning 7% of the table is certainly not an extreme case. 
The exact query criteria are specified by the user in the UI, and can be 
much worse than this case. I have no control over the natur eof the 
query that the user specifies.

2. Yes, if the query is much more selective the index can be a help.

3. The biggest data file in seg0 is 1452572672 bytes in size (e.g. 1.4GB).

4. the index was added after the rows were added.

5. making the index also have the pk_column as the second indexed field 
makes it go like lightning! search runs in 2 secs, about 14x faster.

So in summary it seems like an index will be of no help to me in this 
situation, unless I make it an index of both columns.

Many thanks


Tim Dudgeon wrote:
> 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

View raw message