Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

SQLite as a Key-Value Database

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.

Using SQLite as a Key-Value Database

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

Configurations

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.

Tests

For each configuration listed in the previous section, four different tests are run:

  1. 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.

  2. 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.

  3. 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.

  4. 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).

Test Source Code

Is attached to this wiki page. See below.

Sample Results

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

Attachments:

  • db_vs_sqlite_test.c 13532 bytes added by danielk1977 on 2009-Aug-04 19:10:26 UTC.
    Source code.