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 32KB? >> 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 question. 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 Kasyhup -- 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.
