SQLCipher Benchmarking and Performance Testing

Commercial & Enterprise Edition Feature

SQLCipher Commercial and Enterprise packages contain a special extension which provides a way to benchmark performance characteristics of queries and statements. This feature helps developers to:

1. Quantify the impact of encryption compared to standard non-encrypted databases
2. Analyze performance differences between different security settings
3. Benchmark performance between application or SQLCipher releases
4. Compare the impact of different database designs, index strategies, and content queries to optimize an application.

Test Setup and Execution

The performance testing feature utilizes the following:

• A test configuration table that stores a developer-defined list of statements to run during each test along with information like a friendly name, the number of times to run the statement, and whether to run the statement(s) in a transaction.
• An extension function sqlcipher_perf_setup() which creates the configuration table.
• A results table where the timings for each test are stored.
• An extension function sqlcipher_perf_test() which times each execution, collects various performance statistics, and stores them in the results table.

The overall process for using this feature follows:

First, execute SELECT sqlcipher_perf_setup(); to create the test definition table. The default table name is sqlcipher_perf_tests, but a custom table name can be provided to the function. For example, SELECT sqlcipher_perf_setup('my_tests'); will override the table name to my_tests.

Next, perform an insert into the test definition table for each statement that should be run. The columns of the table follow:

For example, to test the creation of a simple table, followed by the creation of 1000 records without using a transaction, insert the following:

    INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
      VALUES('create_table_t1',
        'CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB);', 1, 0);

    INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
      VALUES('insert_1k_no_transaction',
        'INSERT INTO t1 (a,b,c) VALUES (random() % 100, random() % 100, randomblob(100));', 1000, 0);

Finally, call SELECT sqlcipher_perf_test(); to execute the test. The function will take two optional parameters, first the name of the test definition table, and second the name of a custom results table. If the tests table is not specified, sqlcipher_perf_tests will be used. If the results table is not provided the results will be written to sqlcipher_perf_results by default. The results table will be created automatically if it does not exist, and the results will be inserted. Each row of the results table will include the following information:

Practical Application

Using these basic building blocks it is possible to orchestrate fairly complex tests. By using different attached databases, developers can compare exection using different encryption contexts, database structures, etc. The following example will run a simple baseline test for creating a table, inserting data, and the selecting data. First it will execute it on a standard plaintext database, then on an encrypted database.

SELECT sqlcipher_perf_setup(); -- create the tests table

-- test to create a table
INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
  VALUES('create table','CREATE TABLE IF NOT EXISTS testdb.perftest(a,b,c);', 1, 0);

-- insert 10000 records with a random distribution of IDs in a trasnaction
INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
  VALUES('insert 10000','INSERT INTO testdb.perftest (a,b,c) values (abs(random() % 1000), abs(random() % 1000), hex(randomblob(100)));', 10000, 1);

-- create an index
INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
  VALUES('create index','CREATE INDEX IF NOT EXISTS testdb.a_index ON perftest(a);', 1, 0);

-- select 0.1% of the records using the index, 1,000 times
INSERT INTO sqlcipher_perf_tests (name, sql, iterations, use_txn)
  VALUES('select 0.1% w/ index 1,000 times','SELECT * FROM testdb.perftest WHERE a = abs(random() % 1000);', 1000, 0);

-- Attach a plaintext, non-encrypted database named "testdb" and run the tests on it
-- Store the results in results_plaintext
ATTACH DATABASE 'plaintext.db' AS testdb;
SELECT sqlcipher_perf_test(NULL, 'results_plaintext');
DETACH DATABASE testdb;

-- Attach an encrypted database named "testdb" and run the tests on it
-- Store the results in encrypted_plaintext
ATTACH DATABASE 'encrypted.db' AS testdb KEY 'test123';
SELECT sqlcipher_perf_test(NULL, 'results_encrypted');
DETACH DATABASE testdb;

Once the tests are completed it is possible to compare and contrast the performance differences between the two test runs, e.g. the following query

SELECT 
  a.name, 
  b.time_us - a.time_us AS difference_ms, -- execution time difference in microseconds (μs)
  CAST((CAST(ABS(a.time_us - b.time_us) AS REAL) / CAST(a.time_us AS REAL)) * 100.0 AS INTEGER) AS difference_pct -- percent difference, neatly formatted
FROM 
  results_plaintext a INNER JOIN results_encrypted b ON a.name = b.name

Similarly, using this feature, developers can easily benchmark performance using their own custom data structures and actual application data.