Hi Navjot, Thanks for the response. A little background on the application that I am writing. Currently there is a solution in place that uses flat file with optimized access, meaning there is a whole access mechanism built around these flat files to make accessing them lightning fast. However this technology is getting long in the tooth and does not allow us easy access to the data in human readable format or the ability to run queries on the data. What I am trying to do is replace those flat files with an embedded database, however, I need to match those flat files in access speed to have this new solution be viable in our current model.
What I have tried so far... 1. I increased the CACHE_SIZE but this did not seem to affect the speed of the application. Our queries are very very simple select queries. 2. I implemented an application level caching system. Before going to the DB, the application checks the cache to see if that query has been run before, if so it uses the value stored in the cache. The caching system speeds up the application quite a bit, but since I am trying to replace a system already in place, that does not use caching, I view this as a sort of cheat to beat the old system. What I really want to do is optimize the H2 database so that I can match the speeds of the current flat file solution on reads. Could you elaborate a bit more on your suggestion #1? Our DB is going to be read-only at all times, we don't insert or update any rows. The DB file is created and populated via another program prior to this application. What I was basically after was a caching mechanism similar to the one I put in the application level but on the DB level and after much googling around yesterday it seems that is not offered by H2 or even any embedded DB solution. Thanks for taking a look and for the response. -Mike On Tuesday, March 26, 2013 5:32:40 PM UTC-4, Navjot Singh wrote: > > Hi, > > > > The answer can vary a lot. Also, the relevant cache requirement should be > -- its not the same query, but the result set that the query is going to > return remain same/similar or not. > > > > 1. As you are running in embedded mode, first Q I ask is what kind > of disk are you writing to. If you are writing to RAM disk, then it’s > already in memory for you. If not, then can you think of it as one option. > > 2. If the above is not an option, next option could be setup > CACHE_SIZE. This should depend upon the size of data that your query needs > to churn through to produce your result set. This can eat up your heap but > if you have enough memory, this might be an option. > > 3. If you know the result set is going to be same for some time > then you can setup read-thru cache yourself in simple map based cache with > TTL set to expire as per you app requirement. Or use any caching framework. > There are so many. > > > > HTH > > Navjot Singh > > > > *From:* [email protected] <javascript:> [mailto: > [email protected] <javascript:>] *On Behalf Of *Mike Funaro > *Sent:* 26 March 2013 19:45 > *To:* [email protected] <javascript:> > *Subject:* Cache query results? > > > > HI Everyone, > > > > I started using H2 as an alternative to Derby because of the tremendous > speeds. I am using it in embedded mode. For my application, there is > a possibility of having the same SELECT query run time after time, is there > any kind of mechanism to cache those query results so that another SELECT > query would not have to be run? > > > > If you need more details about how my application operates I would be > happy to elaborate. > > > > Thanks, > > Mike > > > > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > Visit this group at http://groups.google.com/group/h2-database?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
