Hello everyone,
Recently added cipher to my project for obvious security reasons, I’m measuring a singnificant performance decrease.
I’ve already searched for a solution in SQLCipher Performance Optimization and How does SQLCipher compare to other SQLite encryption extensions?
I’ve created a test project to measure time for several insertions (1 million 
) within a transaction.
Setup : Ipad pro (first Gen), simple sqlite database with only one table and 7 attributes.
Here are the results :
Database with page size 4096 - No pragmas
total time to insert 1000000 items : 42.206446125 seconds
Database with page size 4096 - Pragma cipher_memory_security = OFF
total time to insert 1000000 items : 17.3554795 seconds
Database with page size 8192 - PRAGMA cipher_page_size = 8192
total time to insert 1000000 items : 41.802897333 seconds
Database with page size 8192 - PRAGMA cipher_page_size = 8192 + PRAGMA cipher_memory_security = OFF
total time to insert 1000000 items : 17.200024708 seconds
Database with page size 16384 - PRAGMA cipher_page_size = 16384
total time to insert 1000000 items : 42.284837 seconds
Database with page size 16384 - PRAGMA cipher_page_size = 16384 + PRAGMA cipher_memory_security = OFF
total time to insert 1000000 items : 17.174842417 seconds
Conclusion :
I can’t see a real difference tweaking the cipher_page_size, and even with the cipher_memory_security = OFF the time is quite long (this is a test project my database is larger).
Does de page_size of the database has to be changed accordingly ?
Do you guys have any clue to improve this ? is this the expected time for such operations ?
Thanks in advance for your help !
Hi @Crabman
What were your run times for the same operations without specifying a password to the database? If you did not perform those, it would be helpful to do so for a comparison (i.e., plaintext SQLite vs SQLCipher timing.) Additionally, are you able to post the sample on GitHub for further review?
Hello @developernotes,
Thank you for your answer.
The first tests I did were with a base with no encryption and no passphrase.
The times were the same.
The difference is only significant if I stop using sqlCipher. I’m using GRDB :
pod ‘GRDB.swift’ > total time to insert 1000000 items : 7.017373791 seconds
pod ‘GRDB.swift/SQLCipher’
pod ‘SQLCipher’, ‘~> 4.0’ > total time to insert 1000000 items : 42.206446125 seconds
Also I noticed the time it takes to make inserts is linear with sqlcipher :
10000 items : 0.423058292 seconds
100000 items : 4.066482917 seconds
1000000 items : 42.206446125 seconds
And it was not with classic sqlite :
10000 items : 0.115348417 seconds
100000 items : 0.559246 seconds
1000000 items : 7.017373791 seconds
About the test project I’ll try to push this evening
Hello @Crabman - downloaded the sample project and ran it. I don’t have the same device, so instead tested on an iPhone X. A few comments:
- The tests I ran always used
cipher_memory_security = OFF. The reason for this is that your test approaches a worst case scenario with respect to memory utilization and will certainly introduce massive overhead for memory sanitization. Repeatedly calling execute over and over causes redundant statement preparations equal to the number of iterations (see the related article linked below for another example of this same problem). A more memory efficient test would use prepared statements.
- I always created a new database for each test, to measure independently.
That said, when running the project on device, I found the timings of the runs comparable with or without encryption, e.g.
## without key
total time to insert 100000 items : 0.825302166 seconds
total time to insert 1000000 items : 8.900991291 seconds
## with key
total time to insert 100000 items : 1.057804541 seconds
total time to insert 1000000 items : 9.498042791 seconds
Dropping the SQLCipher pod entirely did result in a further time difference, with executions around 0.5 and 5 seconds respectively. However, that is still significantly less of a difference than you observed. There are a variety of potential reasons for that, e.g. further overhead related to inefficient tests, differences in test device performance, differences in the way CocoaPods is building the library vs the system SQLite, etc. From the first set of tests, however, it doesn’t seem like the issue is related to encryption, since that delta is within the expected range.
The related article: When performing transactions to insert data in batches, the efficiency of SQLcipher to insert data is much slower than that of sqlite - SQLCipher / SQLCipher FAQ - Zetetic Community Discussion
Hello @sjlombardo ,
Thank you so much for taking the time, really appreciated.
I did manage to test with an iphone X and i’m getting almost the same results as you for 1 million inserts (6 sec without cipher /12 with it)
The ratio is not the same with my iPad pro (7 sec without /17 with cipher)
So, what I understand is that in this scenario the hardware can significantly change the efficiency of the cipher lib vs classic sqlite.
About the prepared statement it is true that the way i’m doing it relies on memory but I was looking to a way to improve insertions time no mater what.
But actually after running an insertion test of 1M on an “old” iPhone 8 the app just crashes ^^’ so I don’t think this solution is viable on the long run.
I did test using a prepared statement as suggested in this thread but the time to insert is unfortunately increased (the difference between with cipher and without cipher is however reduced.)
My results :
iPhone X
//No cipher
Total time to insert 1000000 items : 5.582551584 seconds
//Cipher
Total time to insert 1000000 items : 12.568741298 seconds
// with cipher + statements
total time to insert 1000000 items : 22.918382458 seconds
//without cipher + statements
total time to insert 1000000 items : 19.783991041 seconds
I’m not sure if this is supposed to be normal or if i’m doing something wrong ^^’
Okay I’m answering myself here :
You are doing something wrong 
As pointed out in this thread :
development builds are slowed down by the liberal use of assertions. Performance tests are only relevant in the release configuration.
So yeah using Statements and testing performance in release was the way to go for both memory safety and fastest insert aproach, I’m increasing performance by 50%-80% just by doing it in release.
Thanks again for your help and time.