db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Few quick questions on Index, Optimizer
Date Thu, 12 Nov 2009 16:06:07 GMT
kashyup wrote:
> Hello all, after going through much docs i think i have lost answers to much
> basic questions. I would appreciate any reply...

Tuning is in general pretty hard, since there are so many factors. Some 
simple answers to get the discussion going below...

> Q1) Derby generates Index on a PK, what is the best/efficient way to rebuild
> index on a PK? 
> Q2) In a certain join query, on the inner query using PK index on a table w/
> 32k records, my Query Optimizer outputs "Number of pages visited=1083". What
> should I change, derby.storage.pageSize or derby.storage.pageCacheSize to
> reduce no. of pages visited?

That would be derby.storage.pageSize. Increasing the page size will 
allow more records to fit onto a single page. Normally you would also 
make sure the page cache is large enough to fit the most commonly used 
pages, otherwise Derby will have to swap pages in and out of the cache a 

> Q3) Why is it that despite doing proper shutdown, if i start my app on derby
> after a day it is very slow the first time. Any thoughts? solution?

What exactly do you mean with the first time?
The very first time you execute the query?
If so, and you shut down the JVM in between, the following have to 
happen the very first time you run a query:
 - load all the Derby classes (this is a significant number)
 - compile the SQL query (i.e. parsing, binding, optimization, code 
 - fetch all data from disk (the caches are empty)
 - to get optimal performance, you must run the queries enough time to 
allow Java HotSpot to optimize them

Assuming you have run the application for a while and the caches are 
warmed up, you don't have to do any of those steps.

Besides from the factors above, I would investigate the query plans as 
Bryan mentioned.

> Q4) Calling CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?) right after the
> app starts on Derby, makes the same queries take much longer once the above
> proc has finished executing. Any thoughts?

Again, just the very first time or for an extended period of time after 
you have compressed the table?

> Thanks all!
> kashup

View raw message