db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Index perfomance
Date Wed, 25 Mar 2009 21:46:27 GMT
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:news@ger.gmane.org] On Behalf Of Tim Dudgeon
> Sent: Wednesday, March 25, 2009 2:30 PM
> To: derby-user@db.apache.org
> 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




Mime
View raw message