OlivaNova application: performance comparison for H2 Database
- 2 minutes read - 365 wordsA couple of days ago I did some performance tests between Oracle 10g (Express) and Oracle TimesTen. As with any commercial products, there is some licensing and costs involved. Obviously, our idea was then to look for a more cost efficient solution in order to get the same results. We specifically looked at H2, a Java SQL Database. This DBMS can operate in several different modes such as embedded, server and in-memory and has compatibility modes for certain commcercial database products like DB2, MySql, MS SQL Server, Oracle and some others. So, looking at the feature list, this seemed to be a good fit.
Installing and setting up a H2 database is very easy. In fact, you don’t even need to install any package. The only thing you actually need is the H2 JAR file. I created an im-memory database with a connection string like this: jdbc:h2:tcp://localhost/mem:db1;DB_CLOSE_DELAY=-1;MODE=ORACLE. What this actually creates is an in-memory database that does not close the database when the last connection to the database has been closed. The MODE option tells H2 to run in an Oracle compatibility mode.
Using the Oracle compatibility mode, I assumed that the code and SQL statements used in the application should be fine and so I executed the same service to create some records in the database. Well, this did not quite work as expected since H2 does not like SQL parameters to be passed with Oracle’s syntax (i.e. “INSERT INTO tableX (field1, field2) VALUES (:1, :2)”). Instead, I had to use quotation marks for the actual values (i.e. “INSERT INTO tableX (field1, field2) VALUES (?, ?)”). Okay, this is easy. I changed this and was able to execute the service.
Again, I created 10.000 records for each iteration. The execution times can be found in the list below.
| Test run | Duration |
|---|---|
| 1 | 02:53,894 |
| 2 | 02:13,929 |
| 3 | 01:57,547 |
| 4 | 02:27,491 |
| 5 | 02:00,328 |
| 6 | 02:29,541 |
| 7 | 02:27,756 |
| 8 | 02:23,789 |
| 9 | 02:35,982 |
| 10 | 02:38,850 |
| Average | 02:24,911 |
When comparing these results with Oracle 10g and Oracle TimesTen, this tells me that H2 was able to execute faster than Oracle 10g, but not as fast as Oracle TimesTen. It is more or less in the middle of this spectrum.