db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kashyup <va...@quantum4d.com>
Subject Re: Few quick questions on Index, Optimizer
Date Thu, 12 Nov 2009 19:28:41 GMT

Thank you guys for your response.
Didn't mention earlier, I am running Derby 10.5.5 as an embedded DB.

Kristian Waagan-4 wrote:
> kashyup wrote:
>> Hello all, after going through much docs i think i have lost answers to
>> much
>> basic questions. I would appreciate any reply...
> Hello,
> 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? 
So what should be the approach?

>> 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 
> lot.
derby.storage.pageSize is set before you issue 'CREATE TABLE/INDEX'
statement, correct?
Also, if my table (initially set with 8KB size) grows (more row inserts)
then does the file size grows? Or a derby file is added for this table of
size 8KB, if so then can I change this setting and increase the size to

>> 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 
> generation)
>  - 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.
Sorry for the certain level of obscurity in my question. I will re-frame my
I run my app on an embedded Derby DB. Closing and starting the app. implies
to closing and starting JVM.
For example, this morning I started my app and did few 'app stuff', the
queries took much longer. I closed my app/JVM, started again and did the
exact same 'app stuff', and the queries were much faster.

P.S. I compared the Query Plans, and they are the same moreover because i am
overwriting the Query Plan for Indexes and Joins (has been very helpful in
my case). 

Does this implies that between the first and second app/jvm start and
shutdown the indexes in file are optimized, files compressed? Couldn't find
answer to what exactly happens. 

The weird part - for the whole day I will not have that slow query issues
until next day morning. I don't shutdown my system. 

>> 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?
I start my app, call compress table and then until i close down my app/JVM
all the qureies take longer to execute. The next time I start my app the
same queries are much faster.

Thanks Bryan, Kristian

View this message in context: http://old.nabble.com/Few-quick-questions-on-Index%2C-Optimizer-tp26311012p26324806.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message