Deleting performance is too slow

SQLCipher Issues
1

When I use SQLcipher and execute ‘delete’ to delete 30000 rows, the performance becomes slower.’ Update 30000 rows 'only takes 900 milliseconds . When I don’t use SQLcipher, it only takes 1300 milliseconds to delete 30000 rows, and it takes 6700 milliseconds to delete 30000 rows using SQLcipher

This is my configuration：

PRAGMA cipher_memory_security = OFF
PRAGMA secure_delete = 0;
PRAGMA journal_mode = WAL;

SQLCipher version: 4.4.3

2

I wish someone could help me

3

I see that SQLcipher forces the use of secure_delete. Could this be the reason for the slow performance? Is there any way not to use secure_delete

4

Hello @qwp767972675 - Large deletes are probably a “worst case” scenario for SQLCipher because they involve reading the database to identify affected rows, decrypting each page, rewriting and scrubbing it, encrypting, and writing back. Here are the general recommendations for performance optimization

SQLCipher Performance Optimization - Zetetic

Specifically from that list, you should make sure the schema is normalized (to minimize size and number of records requiring deletion), appropriate indexes are in place, and that deletes are being grouped up into transactions.

Outside of that, you might be able to get some further performance improvements with one or more of the following

  1. use WAL mode (PRAGMA journal_mode = WAL)
  2. disable secure delete (PRAGMA secure_delete = OFF)
  3. disable HMAC (PRAGMA cipher_use_hmac = OFF)
  4. increase page size (PRAGMA cipher_page_size)
5

What I find strange is that when I change delete to UPDATE, I get a big performance boost, and I set PRAGMA cipher_memory_security = OFF, delete takes 6.7 seconds. The UPDATE takes only 1.2 seconds

6

When you run the update are you actually changing every column of the affected row?

7

Yes, when I run UPDATE, every row is changed

8

As I understand it, SQLITE3 internally just marks the data when SECURE_DELETE is off and doesn’t actually erase the content, so the performance should be similar to UPDATE.

9

Hello @qwp767972675 - can you provide access to a sanitized (clean of sensitive information) standalone database and the associated statements that would reproduce this problem?

10

Thank you for your patient reply. I think I have solved my problem. I tried to use HMAC_SHA1 algorithm, and the encryption library was migrated from Tomcrypt to Open SSL.
-o2 was added to the compile option, using PRAGMA cipher_page_size = 8192; , resulting in a performance improvement of nearly five times