Performance Tests

Performance Test Case TPC-B

TPC-B is a simple OLTP (online transaction processing) stress test which is useful for measuring the sheer performance of update operations.

The official TPC-B specification specifies strict test conditions and reporting requirements for the combination of software / hardware used for the tests. These tests should not be considered as TPC-B tests. The description and measurements follow:

The database consists of several bank branches, each with 10 tellers and 100,000 accounts. A transaction updates a row in each of the branch, teller and account tables, selects the updated row from the account table, and inserts a row into the history table. A total of 5 operations are performed per transaction.

The HyperSQL SVN repository contains the class org.hsqldb.test.TestBench used for these tests. We used a scale factor of 40, therefore there were 40 branches, 400 tellers and 4,000,000 accounts in the database. We performed all tests with 4 connections, each performing 8,000 transactions (32,000 transactions or 160,000 database operations per round). Each test was performed several rounds, one of which is reported here. A dual core 2.5 GHz computer (2008 spec) was used.


You are encouraged to run the tests for yourself and report the results to our Open Discussion Forum or Users mailing list. The test program works with other database engines and can be used for comparison.

The first test uses memory tables and no logging. This demonstrates the sheer speed of the multithreaded engine.



The second test uses memory tables and logs the statement, performing automatic checkpoints (hsqldb.log_size=200) when the log reaches 200 MB. Logging the transactions takes about the same amount of time as performing them. Also when the checkpoint does occur, it slows down the particular test round.


The third test uses cached tables and logs the statements similar to the previous test.

Performance Test Case TPC-C

TPC-C is a more advanced OLTP test compared to TPC-B as there are different types of transactions for order placement, stock level query, etc. and each transaction queries, inserts, or updates multiple rows in several tables. However TPC-C was not designed as a stress tests because it emulates human-operated computer terminals with delays introduced between different stages of transactions. With the dominance of the World Wide Web, this access pattern no longer represents a realistic scenario. There is a Java test suite based on TPC-C here http://jtpcc.sourceforge.net/ and a modified version to use compiled statements here http://sourceforge.net/projects/benchmarksql/. We modified the original test and removed the delays, thus turning it into a stress test.

HyperSQL performed the TPC-C derivative test at 26,000 transactions per minute over a sustained period using memory tables without logging (20,000 with logging). The database was set up with 2 warehouses and eight clients were used in the test.

The test implementation does not look optimised and would certainly perform better (probably twice as fast) if it is modified to use compiled statements. We have not tried the second version.

It would be interesting to run the test documented here http://community.voltdb.com/node/134 which is used by VoltDB, a super-fast next generation RDBMS that actually incorporates the HyperSQL code for processing SQL statements.

See the Performance page for different configurations of HSQLDB and how they affect performance.