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 Tue, 04 Dec 2007 03:26:50 GMT
Hi Brian,

Bryan Pendleton wrote:
>> The fundamental issue here is that in this poor performing case, 
>> derby is not looking at the index on the very large table that would 
>> immediately reduce the dataset.   For whatever reason the optimizer 
>> is making a the worst possible case decision.
>
> Two thoughts:
>
> 1) Have you verified that the statistics are accurate for that table
> and index? Derby only updates the statistics periodically, and under
> certain circumstances. You can call SYSCS_UTIL.SYSCS_COMPRESS_TABLE
> to force a re-update of the statistics, to see if that helps.
That did it!!!  From 22 minutes down to less than a second.  :)

I'm kinda embarassed that I didn't figure it myself.  I had not idea 
that derby didn't keep any stats up-to-date without performing that 
operation explicitly.  Ideally it would keep this up-to-date itself.   
The sys.sysstatistics didn't have any rows in it until I ran the 
compress table operation.

It really needs to be made more prominent in the documentation.  i.e. 
once your database is loaded with representative data, perform the 
compress op for optimal performance.

In the mean-time, we've added a maintenance task to our application to 
do this operation periodically.

Thanks!
Matt

Mime
View raw message