SQLCipher Performance Optimization

There are a few very important guidelines for optimal SQLCipher performance, listed here in rough order of potential impact:

1. Do NOT repeatedly open and close connections. Key derivation is very expensive, by design. Frequent opening / closing of the database connection (e.g. for every query) is a very common cause of performance issues that can usually be easily resolved using a singleton database connection.
2. Use transactions to wrap insert / update / delete operations. Unless executed in a transaction scope, every operation will occur within its own transaction which slows things down by several orders of magnitude.
3. Index columns used for searches or joins. If you don’t, SQLCipher will need to execute full database scans across large numbers of pages
4. Gather statistics to help the query planner. Execute the analyze; command to collect statistics about tables, indices, and stored data to help the query planner make better planning choices for queries.
5. Ensure the database schema is normalized. Using good practices for separation of data into multiple tables will help eliminate redundancy and reduce the number of cryptographic operations that must occur to satisfy a statement. Poor database design results in database bloat which can drastically increase the volume of encryption / decryption operations.
6. Vacuum periodically. Ensuring databases are compacted periodically when you do large deletes, updates etc.

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;

Basic Troubleshooting

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.

Advanced Optimization

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:

• Benchmarking and Performance Testing to provide a way to assess characteristics of specific queries and statements.
• Performance Counters & Statistics to expose a detailed view into the performance impact of SQLCipher’s cryptographic operations, memory utilization, etc.
• Value Level Encryption SQL functions and Encrypted Virtual Tables to allow partial encryption in a database to minimize overhead.

Testing

For more information on performance testing comparisons between SQLCipher and competing encryption providers please see this article.