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
