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
