db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Doran <matt.do...@papercut.com>
Subject Re: FW: Advice on *very* badly performing query
Date Thu, 06 Dec 2007 00:22:27 GMT
Hi Mike,

Mike Matrigali wrote:
> Most of the "stat" information that derby uses is automatically kept up
> to date as part of underlying index and table maintenance.  This info
> includes count of rows in the table and data distribution of data in
> indexes.
That might be the case, but obviously these extra statistics calculated 
during a compress make a big impact on the query optimization.  My case 
involved querying a typical "log" table that had an indexed timestamp 
column.  In the query that ran extremely slowly, we were filtering on 
the indexed date column to only show data for the last week (and doing a 
number of joins).  Without the stats it took 22 minutes, with the stats 
it was sub 1 second.

I'm not sure where these other stats stored (in the index structures 
themselves??), but my DB didn't have any entries in the 
sys.sysstatistics table.  After performing a compress, the statistics 
table was updated, and the query performance improved dramatically.

Thanks,
Matt

Mime
View raw message