This page presents the results of a performance comparison between SQLite and Oracle Berkeley DB. The methodology is loosely based on a performance test conducted by Oracle, the results of which are available here.
Oracle Berkeley DB and SQLite are both embedded database engines that use the host operating systems file-system to store data in. SQLite provides the application programmer with a sophisticated SQL engine for accessing a database stored within a single file. While Oracle Berkeley DB does not provide an SQL engine at all, it does offer advanced features such as replication for high-availability and a more configurable concurrency model than SQLite. This test ignores all that fancy stuff and concentrates on key/value operations in a few simple configurations.
The following two tables are used by these tests to store data. The intkey table uses 64-bit integer keys and arbitrary blobs for values, the blobkey table uses blobs for both key and value. So the functionality provided by the blobkey table is closest to that provided by Oracle BDB. The intkey structure is included in the tests purely because it is implemented as a special case within SQLite.
CREATE TABLE intkey(k INTEGER PRIMARY KEY, v BLOB) CREATE TABLE blobkey(k BLOB PRIMARY KEY, v BLOB)
To emulate the Oracle BDB put() and get() methods on the intkey table, the following SQL statements are used with a 64-bit integer value bound to the SQL :k variable, respectively:
INSERT INTO intkey(k, v) VALUES(:k, :v) SELECT v FROM intkey WHERE k = :k
The SQL queries used to provide the same functionality on the blobkey table are very similar. As one might expect, a blob value is bound to SQL variable :k for the blobkey table queries. The statements used are:
INSERT INTO blobkey(k, v) VALUES(:k, :v) SELECT v FROM blobkey WHERE k = :k
The tests are run with each of the following configurations. It is hoped that each pair of configurations are both typical and roughly equivalent.
The individual who created this page does not know very much about Oracle BDB and may have made mistakes in configuring it. If you can see how the configuration of Oracle BDB can be improved to make the results of these tests more meaningful, please get in touch, either via the SQLite mailing list or by sending e-mail to "dan AT sqlite.org".
|Configuration||SQLite Configuration||Berkeley DB Configuration|
|Transient DB|| An empty string is passed to sqlite3_open() in place
of a file name. This instructs SQLite to open a temporary
file. Additionally, the following pragmas are executed:
PRAGMA journal_mode = off;
|No environment is used. A NULL parameter is passed to the db->open() method to instruct BDB to open a temporary database.|
|Persistent (single-user) DB||
For this test the database is created in the file-system
(not using a temporary file). The following pragmas are
executed to configure SQLite:
PRAGMA journal_mode = off; PRAGMA synchronous = off; PRAGMA locking_mode = exclusive;
|Again no environment is used for this test. The database is created within the file-system (not using a temporary file).|
|Transactional (multi-user) DB||All default options are used for this configuration. The database is created within the file-system.||
An environment is opened using the following flags:
DB_CREATE | DB_INIT_LOCK | DB_INIT_LOG | DB_INIT_MPOOL | DB_INIT_TXN
For further details, refer to the source code at the bottom of this page.
For each configuration listed in the previous section, four different tests are run:
Integer key insert test. In this test, N entries are inserted into the databases using pseudo-randomly generated 64-bit integer keys. The values associated with each key are 100 byte pseudo-randomly generated blobs. The key values are not sorted before they are inserted. For SQLite, entries are inserted into the intkey table. For Oracle BDB, entries are inserted using 8-byte blob keys containing the binary representation of the 64-bit integer.
Integer key select test. This test uses the database generated by the previous test. Each of the N entries inserted into the table is retrieved (looked up) by key value. Keys are retrieved in the same pseudo-random order that they were inserted in.
Blob key insert test. In this test, N entries are inserted into the databases using pseudo-randomly generated 24 byte blob keys. The key values are not sorted before they are inserted. For SQLite, entries are inserted into the blobkey table.
Blob key select test. This test uses the database generated by the previous test. Each of the N entries inserted into the table is retrieved (looked up) by key value. Keys are retrieved in the same pseudo-random order that they were inserted in.
The tests are repeated using three different values of N: 5000, 50000 and 500000.
When tests 1 and 3 are run using the Transactional DB configurations, a separate transaction is used to insert each 1000 entries (so if N==50000 then the data is inserted using 50 separate transactions).
The following results were obtained running on a 32-bit x86 Linux 2.6.27 system with an ext3 file-system.
DB Version: Berkeley DB 4.8.24: (August 14, 2009) SQLite Version: 3.6.19 Testing: 5000 records, Transient DB SQLite DB insert integer key 57695/sec 454504/sec select integer key 345137/sec 690417/sec insert blob key 47163/sec 464986/sec select blob key 183857/sec 649266/sec Testing: 5000 records, Persistent (single-user) DB SQLite DB insert integer key 51874/sec 444168/sec select integer key 334470/sec 684088/sec insert blob key 46093/sec 425061/sec select blob key 183634/sec 590737/sec Testing: 50000 records, Transient DB SQLite DB insert integer key 49766/sec 351000/sec select integer key 261159/sec 476894/sec insert blob key 41629/sec 336806/sec select blob key 150527/sec 465670/sec Testing: 50000 records, Persistent (single-user) DB SQLite DB insert integer key 49635/sec 341476/sec select integer key 268691/sec 477920/sec insert blob key 41457/sec 326038/sec select blob key 148248/sec 453597/sec Testing: 5000 records, Transactional DB SQLite DB insert integer key 36720/sec 49536/sec select integer key 69611/sec 339881/sec insert blob key 28525/sec 56251/sec select blob key 60767/sec 313558/sec Testing: 50000 records, Transactional DB SQLite DB insert integer key 4949/sec 48729/sec select integer key 67449/sec 260517/sec insert blob key 8403/sec 37934/sec select blob key 57660/sec 240469/sec Testing: 500000 records, Transient DB SQLite DB insert integer key 7186/sec 10503/sec select integer key 111043/sec 53575/sec insert blob key 23178/sec 5704/sec select blob key 96063/sec 55766/sec Testing: 500000 records, Persistent (single-user) DB SQLite DB insert integer key 10118/sec 5996/sec select integer key 108121/sec 47929/sec insert blob key 21322/sec 8792/sec select blob key 94154/sec 42691/sec