Performance problems using Squeryl and H2 database for a desktop application

Question

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.

Question

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?

Answer 1

The solution was very simple in the end. I'll quote the FAQ.

Delayed Database Closing

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.

Answer 2

Looking at the screeshot it seems you are selecting from the DB inside the getValueAt() method of your TableModel (the method name getRowAt() at the top of the call stack causes this assumption of mine).

If my assumption is correct, than this is the your main problem. getValueAt() is called by the JTable's paint() method constantly (probably several times a second), so that should be as quick as possible.

You should get the data for your JTable in a single SQL query and then save the result in a some data structure (e.g. an ArrayList or something like that).

I don't know Squeryl, but I doubt you really need to wrap every SELECT into a transaction. From the stacktrace it appears that this causes massive write in H2. Did you try to run the SELECTs without explicitely opening (and closing) a transaction each time?