There are a few very important guidelines for optimal SQLCipher performance:
- Do not repeatedly open and close connections, as 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.
- Use transactions to wrap insert / update / delete operations. Unless executed in a transaction scope, every operation will occur within it’s own transaction which slows things down by several orders of magnitude.
- Ensure your data is normalized (i.e., using good practices for separation of data into multiple tables to eliminate redundancy). Unnecessary duplication of data leads to database bloat, which means more pages for SQLCipher to operate on.
- Execute the
analyze;command to gather statistics about tables, indices, and stored data to help the query planner make better planning choices for queries.
- Ensure that any columns that are used for searches or join conditions are indexed. If you don’t, SQLCipher will need to execute full database scans across large numbers of pages
- Vacuum periodically to ensure databases are compact if you do large deletes, updates etc.
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.