db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Selecting a few rows is really slow when ordering by primary key
Date Thu, 02 Feb 2012 10:45:01 GMT
On 02.02.2012 11:14, Kristian Waagan wrote:
> On 01.02.2012 14:51, Rick Hillegas wrote: 
< snip>
>> On 2/1/12 4:07 AM, Simon Chatelain wrote:
< snip>
> You may want to set derby.storage.indexStats.log=true, and more 
> importantly, derby.storage.indexStats.debug.trace=true. Run you app 
> for a while, then grep for "{istat}" in derby.log.
> This will tell you if automatic calculation is being triggered.
>
> Although strongly discouraged for production environments, you can try 
> to set lndiffThreshold to zero, or a very small value, to schedule an 
> update on every occasion, i.e. when you compile a statement like 
> "select ... from tbl where indexed_column = ?".

If you want to test this manually, you will appreciate setting 
derby.storage.indexStats.debug.absdiffThreshold, and possibly 
derby.storage.indexStats.debug.createThreshold, to a lower value (both 
express number of rows, defaults are 1000 and 250 respectively).

Just for reference, testing stuff like this in ij can be done along 
these lines:
select * from sys.sysstatistics (note creationtimestamp / statistics, 
may want a join to get table name)
insert into ... (or delete from ...)
prepare q as 'select * from <table> where <indexed_column> = ?'
select * from sys.sysstatistics


-- 
Kristian

>
> Note that the istat daemon will only be triggered when the table 
> changes in size. If you do updates that changes the index key 
> distribution significantly you'll want to generate new statistics 
> manually/periodically.
>
>
> Regards,

Mime
View raw message