-
Notifications
You must be signed in to change notification settings - Fork 26
Performance
James Moger edited this page Apr 9, 2016
·
1 revision
The information provided here may be based on flawed test procedures. You have to be the judge of what is performant and non-performant.
Performance of iciql statement generation is not currently benchmarked.
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.
*******************************************************************************
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)
-------------------------------------------------------------------------------