Performance
The information provided here may be based on flawed test procedures. You have to be the judge of what is performant and non-performant.
Statement generation#
Performance of iciql statement generation is not currently benchmarked.
Performance comparison#
The following data was generated by running the single-threaded iciql test suite. All database connections are pooled and re-used within each execution of the test suite using Apache Commons DBCP.
Connections are pooled to normalize embedded database performance with out-of-process database performance. Some of the Java embedded database configurations have a very high startup-time penalty. Notably, H2 is slow to open a database and its performance is substantially affected if connection pooling is not enabled to keep the embedded database open.
SQLite uses the default DELETE journaling mode. About a 4x performance improvement can be achieved when using the WAL journaling mode (PRAGMA journal_mode = WAL).
External process databases (MySQL & PostgreSQL) use the default settings as provided by the platform.
All tables are created as CACHED when the database distinguishes between CACHED and MEMORY tables.
All performance numbers include the combined overhead of iciql statement generation and JUnit 4 test framework execution so they are not bare-metal database metrics.
All bulk operations (insertAll, updateAll, deleteAll, etc) automatically create savepoints, if supported by the JDBC connector, to help ensure atomicity of the operation.
Results#
******************************************************************************* iciql 2.0.0 (2016-04-04) testing 18 database configurations ******************************************************************************* java.vendor Oracle Corporation java.runtime.version 1.8.0_77-b03 java.vm.name Java HotSpot(TM) 64-Bit Server VM os.name Linux os.version 3.13.0-24-generic os.arch amd64 available processors 4 available memory 1772.5 GB ------------------------------------------------------------------------------- Testing H2 1.4.191 (2016-01-21) jdbc:h2:mem:iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,872 statements in 1.036 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing H2 1.4.191 (2016-01-21) jdbc:h2:file:/home/james/git/iciql/testdbs/h2/iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,872 statements in 0.551 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing H2 1.4.191 (2016-01-21) jdbc:h2:tcp://localhost//home/james/git/iciql/testdbs/h2tcp/iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,872 statements in 1.015 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing HSQL 2.3.3 jdbc:hsqldb:mem:iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.569 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing HSQL 2.3.3 jdbc:hsqldb:file:testdbs/hsql/iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.979 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing HSQL 2.3.3 jdbc:hsqldb:hsql://localhost/iciql ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 2.773 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing Derby 10.12.1.1 - (1704137) jdbc:derby:memory:iciql;create=true ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,840 statements in 3.938 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing Derby 10.12.1.1 - (1704137) jdbc:derby:directory:testdbs/derby/iciql;create=true ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,840 statements in 17.354 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing Derby 10.12.1.1 - (1704137) jdbc:derby://localhost:1527/testdbs/derby/iciql;create=true ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,840 statements in 19.920 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing MySQL 5.6.28-0ubuntu0.14.04.1 jdbc:mysql://localhost:3306/iciql ------------------------------------------------------------------------------- 94 tests (1 failures, 3 ignores) 4,836 statements in 21.292 secs + testIndexCreation(com.iciql.test.AnnotationsTest) null ------------------------------------------------------------------------------- Testing PostgreSQL 9.5.2 jdbc:postgresql://localhost:5432/iciql ------------------------------------------------------------------------------- 94 tests (3 failures, 3 ignores) 4,824 statements in 7.002 secs + testBooleanColumn(com.iciql.test.BooleanModelTest) ERROR: column "mybool" is of type boolean but expression is of type integer Hint: You will need to rewrite or cast the expression. Position: 33 + testIntColumn(com.iciql.test.BooleanModelTest) ERROR: column "mybool" is of type integer but expression is of type boolean Hint: You will need to rewrite or cast the expression. Position: 33 + testPrimitiveShortBooleanColumn(com.iciql.test.BooleanModelTest) ERROR: column "mybool" is of type boolean but expression is of type smallint Hint: You will need to rewrite or cast the expression. Position: 33 ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:file::memory:?cache=shared&foreign_keys=ON ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.265 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=DELETE&synchronous=FULL ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 37.637 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=DELETE&synchronous=NORMAL ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 27.159 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=DELETE&synchronous=OFF ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.483 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=WAL&synchronous=FULL ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 11.710 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=WAL&synchronous=NORMAL ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.455 secs 100% successful test suite run. ------------------------------------------------------------------------------- Testing SQLite 3.8.11 jdbc:sqlite:/home/james/git/iciql/testdbs/sqlite/iciql.db?foreign_keys=ON&journal_mode=WAL&synchronous=OFF ------------------------------------------------------------------------------- 94 tests (0 failures, 3 ignores) 4,836 statements in 0.288 secs 100% successful test suite run. ******************************************************************************* iciql 2.0.0 (2016-04-04) test suite performance results on Oracle Corporation 1.8.0_77-b03, Linux 3.13.0-24-generic, amd64 ******************************************************************************* Name Config Version Stats/sec Runtime ------------------------------------------------------------------------------- SQLite memory 3.8.11 18249 0.3s (1.0x) SQLite wal,no_sync 3.8.11 16791 0.3s (1.1x) SQLite wal,norm_sync 3.8.11 10628 0.5s (1.7x) SQLite delete,no_sync 3.8.11 10012 0.5s (1.8x) H2 file 1.4.191 (2016-01-21) 8842 0.6s (2.1x) HSQL memory 2.3.3 8499 0.6s (2.1x) HSQL file 2.3.3 4939 1.0s (3.7x) H2 tcp 1.4.191 (2016-01-21) 4800 1.0s (3.8x) H2 memory 1.4.191 (2016-01-21) 4702 1.0s (3.9x) HSQL tcp 2.3.3 1743 2.8s (10.5x) Derby memory 10.12.1.1 - (1704137) 1229 3.9s (14.9x) PostgreSQL tcp 9.5.2 688 7.0s (26.4x) SQLite wal,full_sync 3.8.11 412 11.7s (44.2x) Derby file 10.12.1.1 - (1704137) 278 17.4s (65.5x) Derby tcp 10.12.1.1 - (1704137) 242 19.9s (75.2x) MySQL tcp 5.6.28-0ubuntu0.14.04.1 227 21.3s (80.3x) SQLite delete,norm_sync 3.8.11 178 27.2s (102.5x) SQLite delete,full_sync 3.8.11 128 37.6s (142.0x) -------------------------------------------------------------------------------