SQLCipher Performance Optimization

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.

If your application 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.

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

4 Likes

I am using a singleton object to perform database operations. I made bulk insertion demo and making insertion by both SQLCipher library with empty encryption key("") & DataBaseHelper.

I observed that DataBaseHelper taking less time than SQLCipher for bulk insertions.

Can you please elaborate what extra things doing with SQLCipher at time of db operations. Is there any way to overcome this issue?

Note: I have 4 databases, 2 out of 4 are needs encryption and other 2 doesn’t need encryption.
But i am using common access helper class for use Databases.
Where encryption doesn’t required, I pass ("") empty string as an argument to open database. Is it correct way of use SQLCipher for non-encrypted database. Whats your suggestion for this kind of requirement?

After examines both SQLCipher and android default DatabaseHelper, i come up with one conclusion is that, SQLCipher takes sightly more time than DataBaseHelper for db operations. Is it right?

Kindly, suggest me solutions.

Thanks

Hello @Rohan_Patel

SQLCipher is doing more that the default SQLite implementation in that it is encrypting the contents of your data as you perform inserts. In terms of your specific slow down, it would be difficult to discern the cause without further information. Have you tried the recommendations above to identify the timing of the specific queries you are executing on the database?

I have also performed single record insertion with both. DatabaseBaseHelper takes 11-19 milliseconds and SQLCipher takes 21-29 milliseconds to execute query with opening db.

I performed this action several times, in each time SQLCipher looks more expensive compare to DataBaseHelper.

Hello @Rohan_Patel

Can you share the schema and sample data you are using for your timings? Have you tried running this type of scenario as a test through the SQLCipher for Android test suite?

@Rohan_Patel SQLCipher does have extra overhead for encryption. It would be impossible for the library to perform all of its operations with exactly the same performance as standard SQLite. That said, you mentioned that you are seeing poor performance on bulk inserts. I have a few comments and questions:

  1. Are you starting the measurement after the database has been opened? Opening the database is a deliberately slow process due to key derivation, as mentioned earlier.

  2. When doing inserts, are you batching up multiple inserts into a single transaction? If not, you will see much poorer performance. Be sure to wrap the inserts into a transaction.

Hi Nick,
Thanks for sharing that. Could you please elaborate point 2 and 4.

Hello @sharmasneha

If you are new to transactions within SQLite as referenced in item 2, I would recommend reading both this page and this page for more details.

With regard to indexes covered in item 4, I would recommend reviewing this page.

1 Like

Hi Nick, Thanks for your help. It worked.

I know this topic is quite old. However I have wonder if I understand the operation of sqlcipher correctly.
When the connection to the database is opened and the key derivation has been done, the content of the database is still not readable through a hex dump, correct?
I wasn’t sure about that and to protect the data, I opened and closed the database connection with every query, which of course results in very slow performance.

Thanks for your help.

Cheers Mav

Hello @Maverick2805

Correct, the content that is written to disk is encrypted (when operating on a SQLCipher encrypted database). When a SQL query is executed, SQLCipher will read pages within the database and decrypt the content in memory to resolve the query.

If possible, it is better to maintain an open connection for each query to avoid the time cost associated with the key derivation process.

Thank you very much, exactly what I needed to know. Like that I rewrote the application again to use the database as a singleton and only open it once, but still have the data encrypted.
Cheers
Mav

A post was split to a new topic: What is the purpose of PRAGMA cipher_memory_security?

I’ve just switched from the Android platform SQLite to sqlcipher but using null passphrase (as a first step in migration) and noticed a major slowdown in populating databases. I was expecting the points mentioned above to be only relevant when using a passphrase, but I guess not. Which of the above points are relevant when using null passphrase?

UPDATE: the PRAGMA cipher_memory_security = OFF brings the speed back to a reasonable level.

@marcardar - yes, the memory security feature hooks into the memory allocation system so it will operate even with unencrypted databases. Setting PRAGMA cipher_memory_security = OFF; should remove that performance impact.

@sjlombardo understood, thanks. Would it be better for it to be the automatic default when there is no encryption?

Hi @marcardar, I totally agree that would be nice, but unfortunately the memory management system is low level and global to the library. Thus the memory security can’t be applied separately for non-encrypted connections. If it was off by default, or only turned it on after an encrypted database was opened, there could be edge cases in which memory segments could be treated inconsistently. As a result we opted to enable it by default.

1 Like

Is there any performance benefit of using PRAGMA cipher_use_hmac = OFF? Or is it only necessary for when opening legacy databases (or databases created with the same PRAGMA)?