HSQLDB Pure Java Database

HSQLDB Performance Summary

The following charts show the performance of HSQLDB (in green) relatively to other JPA/DBMS combinations (in gray).

Position #1 on the X-Axis (the fastest) is about 100 times faster than position #32 (the slowest).
In the chart below the Y-Axis represents result score (maximum 100) in logarithmic scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB
In the chart below the Y-Axis represents result score (maximum 100) in linear scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB


The configurations in which HSQLDB has been tested are listed in the following table:

 Database + JPA ProviderNormalized Score
100 - Best,  0 - Worst
Final Position
1 - Best,  32 - Worst
Failed Tests
1EclipseLink with HSQLDB embedded14.64  (out of 32)2
2Hibernate with HSQLDB embedded11.08  (out of 32)0
3OpenJPA with HSQLDB embedded8.414  (out of 32)0
4DataNucleus with HSQLDB embedded4.324  (out of 32)13

As shown above, the performance and stability are highly dependent on the JPA provider. HSQLDB is much faster when used with Hibernate than with any other JPA implementation. The combination of HSQLDB with EclipseLink seems less stable and results in too many exceptions.

About HSQLDB Database

HSQLDB (Hyper Structured Query Language Database) is a popular pure Java RDBMS.

The first version of HSQLDB was released in 2001, based on the discontinued HypersonicSQL database engine.

HSQLDB is available under the BSD open source license.

In this benchmark HSQLDB version 2.0.1-rc2 has been tested. Cached tables have been used, since the default memory tables are mainly for small databases (even if the RAM is large enough to hold the entire database - opening and closing a large database with memory tables is very slow).

The HSQLDB database can be used in client-server and embedded modes. Only the embedded mode has been tested in this benchmark due to some difficulties in using JPA against the HSQLDB 2.0 database server.

Solved Problems and Issues

The stack traces below demonstrate some exceptions that have been thrown when using HSQLDB in previous runs of this benchmark and have been fixed by upgrading software and setting.

javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: user lacks privilege or object not found: PERSON
Error Code: -5501
Call: INSERT INTO PERSON (ID, MIDDLENAME, LASTNAME, ZIP, PHONE, LASTLOGINDATE, STREET, STATE, LOGINCOUNT, CITY, COUNTRY, JOINDATE, EMAIL, BIRTHDATE, FIRSTNAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [1, CIPUPKQIKER, QGSHJQRJRQY, 708733964, 134036188972027, 1983-01-05, NYMIGRADOXLQ, ZT, 94, QEJSJKOIVRYODJXSU, KTJPTPAIWD, 1950-03-07, QXRE@EFM.YI, 1947-10-31, SLCIBTPWESS]
Query: InsertObjectQuery(SLCIBTPWESS CIPUPKQIKER QGSHJQRJRQY)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
	at org.jpab.Test.persist(Test.java:216)
	at org.jpab.Test.persist(Test.java:199)
	at org.jpab.Runner$PersistAction.run0(Runner.java:528)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: user lacks privilege or object not found: PERSON
Error Code: -5501
Call: INSERT INTO PERSON (ID, MIDDLENAME, LASTNAME, ZIP, PHONE, LASTLOGINDATE, STREET, STATE, LOGINCOUNT, CITY, COUNTRY, JOINDATE, EMAIL, BIRTHDATE, FIRSTNAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [1, CIPUPKQIKER, QGSHJQRJRQY, 708733964, 134036188972027, 1983-01-05, NYMIGRADOXLQ, ZT, 94, QEJSJKOIVRYODJXSU, KTJPTPAIWD, 1950-03-07, QXRE@EFM.YI, 1947-10-31, SLCIBTPWESS]
Query: InsertObjectQuery(SLCIBTPWESS CIPUPKQIKER QGSHJQRJRQY)
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:683)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:980)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:206)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:341)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:465)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:290)
	at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:736)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:643)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2909)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1291)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1273)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1233)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:123)
	at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3348)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1422)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:546)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1527)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:200)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1148)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
	... 6 more
Caused by: java.sql.SQLException: user lacks privilege or object not found: PERSON
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1401)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1350)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:652)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:576)
	... 35 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: PERSON
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.SchemaManager.getTable(Unknown Source)
	at org.hsqldb.ParserDQL.readTableName(Unknown Source)
	at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
	at org.hsqldb.Session.compileStatement(Unknown Source)
	at org.hsqldb.StatementManager.compile(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 41 more
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250)
	at org.jpab.node.NodeTest.retireveEntities(NodeTest.java:130)
	at org.jpab.Test.doAction(Test.java:250)
	at org.jpab.Runner$RetrieveAction.run0(Runner.java:540)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2452)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
	at org.hibernate.loader.Loader.list(Loader.java:2187)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241)
	... 5 more
Caused by: java.sql.SQLException: data type cast needed for parameter or null literal
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
	at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1616)
	at org.hibernate.loader.Loader.doQuery(Loader.java:717)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
	at org.hibernate.loader.Loader.doList(Loader.java:2449)
	... 13 more
Caused by: org.hsqldb.HsqlException: data type cast needed for parameter or null literal
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.FunctionSQL.resolveTypes(Unknown Source)
	at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
	at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
	at org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
	at org.hsqldb.QuerySpecification.resolveTypesPartOne(Unknown Source)
	at org.hsqldb.QuerySpecification.resolveTypes(Unknown Source)
	at org.hsqldb.QueryExpression.resolve(Unknown Source)
	at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
	at org.hsqldb.Session.compileStatement(Unknown Source)
	at org.hsqldb.StatementManager.compile(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 22 more
org.apache.openjpa.persistence.RollbackException: integrity constraint violation: unique constraint or index violation; SYS_PK_10037 table: OPENJPA_SEQUENCES_TABLE {prepstmnt 25139768 INSERT INTO OPENJPA_SEQUENCES_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) [params=(String) DEFAULT, (int) 0]} [code=-104, state=23505]
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:583)
	at org.jpab.Test.persist(Test.java:216)
	at org.jpab.Test.persist(Test.java:199)
	at org.jpab.Runner$PersistAction.run0(Runner.java:528)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by:  org.apache.openjpa.persistence.PersistenceException: integrity constraint violation: unique constraint or index violation; SYS_PK_10037 table: OPENJPA_SEQUENCES_TABLE {prepstmnt 25139768 INSERT INTO OPENJPA_SEQUENCES_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) [params=(String) DEFAULT, (int) 0]} [code=-104, state=23505]
	at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4827)
	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4787)
	at org.apache.openjpa.jdbc.sql.HSQLDictionary.newStoreException(HSQLDictionary.java:318)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
	at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:66)
	at org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
	at org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:794)
	at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:487)
	at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:463)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:778)
	at org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
	at org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:605)
	at org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:2952)
	at org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:40)
	at org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1047)
	at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2077)
	at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2037)
	at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1955)
	at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
	at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1479)
	at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:925)
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:559)
	... 5 more
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: integrity constraint violation: unique constraint or index violation; SYS_PK_10037 table: OPENJPA_SEQUENCES_TABLE {prepstmnt 25139768 INSERT INTO OPENJPA_SEQUENCES_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) [params=(String) DEFAULT, (int) 0]} [code=-104, state=23505]
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:257)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:233)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:70)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1079)
	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq.executeUpdate(TableJDBCSeq.java:816)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq.insertSequence(TableJDBCSeq.java:501)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq.access$100(TableJDBCSeq.java:74)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq$AllocateSequenceRunnable.run(TableJDBCSeq.java:902)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq.allocateSequence(TableJDBCSeq.java:454)
	at org.apache.openjpa.jdbc.kernel.TableJDBCSeq.nextInternal(TableJDBCSeq.java:305)
	at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
	... 23 more