1

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?

Community
  • 1
  • 1
ziggystar
  • 28,410
  • 9
  • 72
  • 124
  • I don't know why it's so slow, but the question is, if you need only so small db (up to few MB) maybe you can use in memory H2 instance (copied from hdd at startup of the application)? Then you won't have problem with disk IO at all... – Piotr Kukielka Jan 14 '12 at 21:31
  • @kuki That's basically a good idea. Though I like how the (rare) write access is instantly on disk now; and no need to save everything explicitly. – ziggystar Jan 15 '12 at 17:21

2 Answers2

3

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?

  • Your assumptions are correct. And locally caching the data at the TableModel works (once I had this as a workaround; this is a viable solution). But not doing this caching is cleaner and I thought there must be a way so I don't have to cache. And for your last paragraph I think this is the solution but this is Squeryl-specific, since the transactions are handled by Squeryl. I don't know how to do this. – ziggystar Jan 15 '12 at 09:29
  • 2
    @ziggystar: from Swing's UI system point of view, doing the caching is the cleaner solution. As you have no control over *when* Swing repaints your component (or parts of it) any method involved in the painting should be as quick as possible. It's one of the key points in creating a responsive Swing application. –  Jan 15 '12 at 09:34
  • I'm again using a cache for the table model. But there are more (if not so many) db accesses scattered throughout my application. What I don't want to do is cache the whole DB. If I did so I could simply write everything without the DB. Maybe that's even the way to go... – ziggystar Jan 16 '12 at 09:45
  • @ziggystar: you can create all tables as "memory" tables to let H2 full cache the db in memory. –  Jan 16 '12 at 09:50
  • But then I need to load everything on startup and save everything in the end. Is this simple to do with H2? Currently I'm thinking about trying doing everything in one session and flushing after each write. Do you think this could solve my problems? I still have to figure out how to do this using Squeryl. – ziggystar Jan 16 '12 at 10:09
  • @ziggystar: H2 will handle that for you. You just need to make sure to shutdown H2 properly. –  Jan 16 '12 at 10:11
  • So, is this equivalent to having a single session and a large enough cache? I actually don't know the difference between creating/closing a session and starting/shutting down H2. I will try at least one of the approaches this evening. If one works out I'll accept your answer if you include these two suggestions (in memory tables/having only one session). – ziggystar Jan 16 '12 at 10:23
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.

Community
  • 1
  • 1
ziggystar
  • 28,410
  • 9
  • 72
  • 124
  • Is this a common problem with H2? Why does Squeryl close the connection, what about connection pooling, would that solve the problem? – Phil Jan 21 '12 at 08:27
  • @Phil I have no idea which different strategies exist for connecting to a DB. I doubt that pooling would solve the problem since it is a symptom of having "too few" transactions and there a phases in my program where there is no DB interaction going on. I don't know how to tell Squeryl to keep the session open. – ziggystar Jan 21 '12 at 12:06