SQLCipher Performance Optimization

SQLCipher SQLCipher FAQ
1

Optimization Guidelines

The latest guidance has been moved to the SQLCipher Performance Optimization documentation. This topic has been archived to preserve discussion.

4 Likes
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.

1 Like
9

Hi Nick, Thanks for your help. It worked.

10

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

11

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.

12

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

13

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

14

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.

15

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

16

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

17

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
18

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)?