Most applications find that SQLCipher has low overhead and great performance. It’s not unusual to see as little as 5-15% overhead for SQLCipher encryption. However, performance can vary significantly based on factors like application design, database size, schema, query complexity, and platform. This document will provide information on how to maximize SQLCipher’s speed, fix performance issues, and troubleshoot hotspots.
There are a few very important guidelines for optimal SQLCipher performance, listed here in rough order of potential impact:
analyze; command to collect statistics about tables, indices, and stored data to help the query planner make better planning choices for queries.SQLCipher’s official Commercial and Enterprise packages feature heavily optimized cryptographic modules and provider interfaces. In some cases official packages can be up to 4-times faster that community edition counterparts for certain workloads and platforms. If you are experiencing performance issues with SQLCipher Community Edition consider evaluating a 15-day free trial of official packages as a next step.
SQLCipher provides advanced security features to protect against brute force, dictionary, and pre-computation attacks. The default opts for the most secure settings, using an expensive key derivation process (PBKDF2) to generate the actual encryption key from the provided key material and a random per-database salt.
However, this key deviation process is very slow, and can cause a substantial delay for the first operation that access a database after the connection is opened and the key is set. If an application uses a limited number of long-lived connections, this will not present a problem. However, in other cases is may cause performance issues.
An application can avoid the expense of key derivation entirely by using a RAW key , e.g. PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'"; so that SQLCipher skips key derivation.
Alternately an application can reduce the performance of key derivation by decreasing the number of Key Derivation rounds used by SQLCipher via PRAGMA kdf_iter. For using example, PRAGMA kdf_iter = 64000; would reduce the apparent impact of key derivation by a factor of four from the default (256,000 rounds).
This change may substantially improve the performance of SQLCipher. However, while the underlying encryption security of SQLCipher is the same whether you are using a raw key or derived passphrase, disabling derivation is only appropriate where the key material provided to SQLCipher is already high-entropy and not susceptible to attach (e.g. a random key). This adjustment is not recommended when using passwords, passphrases, short keys, or other low-entropy key values.
SQLCipher implements an additional security feature to prevent tampering and forgery. Each page data is protected by a HMAC “signature”, such that if any data is modified externally or resequenced SQLCipher will be able to detect the invalid change.
This validation involves considerable additional processing overhead whenever pages are read or written, which can be expensive. For improved performance you can disable HMAC using PRAGMA cipher_use_hmac = OFF; after setting the key, but before using the database.
This change may substantially increase SQLCipher performance for applications with heavy read/write loads. However, it does impact the overall security of the solution, especially when opening databases from untrusted sources, and should be used with caution.
SQLCipher uses a default page size of 4096 bytes for encrypted databases. Depending on the structure of a database this can have a significant affect on performance, particularly if an application is storing large BLOB values. Typically a smaller page size will perform well with operations that affect a small amount of data, because less information must be encrypted / decrypted to satisfy a statement. If statements are operating on large amounts of data then a larger page size may be more efficient (due to a lower number of overall operations required).
SQLCipher does allow you the flexibility of changing the page size used via PRAGMA cipher_page_size.
Determining the best page size is therefore an application-specific performance tuning exercise.
SQLCipher’s Memory Security feature provides improved sanitization of in-memory data used by the database. However, it is very time consuming to wipe all memory allocations. As such, SQLCipher’s enhanced memory security feature is turned off by default as of SQLCipher 4.5.0. If your application is using an older version and deals with large amounts of data (e.g. full table scans, large text values, BLOBS) then it may be desirable to disable the memory security and wiping feature with the following PRAGMA:
PRAGMA cipher_memory_security = OFF;
To diagnose performance problems with specific query statements, it may be helpful to run an explain query plan command against specific queries. The output of the explain query command is described here.
If you are uncertain of what queries are performing poorly, SQLCipher includes a PRAGMA called cipher_profile that allows for profiling queries and their respective execution time in milliseconds.
SQLCipher Commercial and Enterprise packages are specially optimized and may be up to 3-4x faster than free Community Edition packages. They also include advanced features that can help get the best possible performance out of SQLCipher like:
For more information on performance testing comparisons between SQLCipher and competing encryption providers please see this article.