SQLCipher Performance


#1

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.


SQLCipher for Android 3.3.1-2 Release
SQLCipher for Android was getting slow
How to add and decrypt a pre encrypted sqlcipher sqlite db in ios step by step?
Not able to read Data from Encrypted Database in IOS
Does sqlcipher windows supports encryption for memory table?
3.5.4->3.5.7 migration issues with scrypt
Internal Encryption mechanism and Library used in SQLCipher
Using the SQLite Online Backup API
How to change PBKDF2_ITER in the process of building linux so
Not able to read Data from Encrypted Database in IOS
SQLCipher for Android was getting slow
Does sqlcipher windows supports encryption for memory table?
#2

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


#3

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?


#4

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.


#5

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?


#6

@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.


#7

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


#8

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.


#9

Hi Nick, Thanks for your help. It worked.