I have a desktop application that persists its data in a local H2 database. I am using Squeryl to interface to the database.
The size of the database is very small (some 10kB). I'm experiencing severe performance problems and there is extensive disk IO going on. I am only reading the DB and thus I expected that the complete data could be cached; I even set the cache size to some value (way higher than total db size). Also I tried disabling locking with no result.
My program performs very many small queries on the database; basically I have a Swing TableModel that makes a query for every table entry (each column of each row). I'm wrapping each of those calls into a Squeryl transaction block.
I've made a profile using JVisualVM and I suspect the following call tree shows the problem. The topmost method is a read access from my code.
link to JVisualVM screen shot.
How can I fix this or what am I doing wrong? Somehow I expect that I should be able to make many small calls to a DB that is small enough to be held in under 1MB of memory. Why is this disk IO going on and how can I avoid it?
The solution was very simple in the end. I'll quote the FAQ.
Usually, a database is closed when the last connection to it is closed. In some situations this slows down the application, for example when it is not possible to keep at least one connection open. The automatic closing of a database can be delayed or disabled with the SQL statement SET DB_CLOSE_DELAY <seconds>. The parameter <seconds> specifies the number of seconds to keep a database open after the last connection to it was closed. The following statement will keep a database open for 10 seconds after the last connection was closed:
SET DB_CLOSE_DELAY 10
The value -1 means the database is not closed automatically. The value 0 is the default and means the database is closed when the last connection is closed. This setting is persistent and can be set by an administrator only. It is possible to set the value in the database URL: jdbc:h2:~/test;DB_CLOSE_DELAY=10.