we are seeing a drastic performance loss after using SQLCipher 4.2 in our android app.
Even in case we don’t provide an encryption key and just use SQLCipher without encryption
our app performances about 300 percent slower as if we use the FrameworkSQLite Database.
Are there any connection parameter that we could try to change to boost the performance?
What is the difference between the native sqlLite engine that is shipped with Android and the one that is used by SqlCipher?
For example this query: SELECT id FROM tableTest;
takes about 8600 ms to finish
where tableTest is defined by: CREATE TABLE IF NOT EXISTS tableTest (id BIGINT NOT NULL PRIMARY KEY, smallText VARCHAR, mediumText VARCHAR);
with about 76 000 rows, smallText is less than 20 chars wide and mediumText is less than 300 chars wide
target sdk is 29
the device we are testing on is a samsung T515 tablet
we don’t see much cpu usage in the profiler
this is how we define import sqlcipher android wrapper:
query: SELECT id FROM tableTest;
where tableTest is defined by: CREATE TABLE IF NOT EXISTS tableTest (id BIGINT NOT NULL PRIMARY KEY, smallText VARCHAR, mediumText VARCHAR);
with about 76 000 rows, smallText is less than 20 chars wide and mediumText is less than 300 chars wide
this are the results of PRAGMA cipher_profile:
SQLCipher with key provided: 8647 ms
SQLCipher without key provided: 8731 ms
android nativ: 59 ms
Are these timing solely through usage of the Support API? If so, would you try timing your query through the standard SQLCipher for Android client library for comparison and share your results? Thanks!
One way the performance with SQLCipher differs significantly from the default Android SQLite API is with concurrent database operations. SQLCipher’s Android API has a locking behavior that has been improved in the default API since API 16 (Jelly Bean). More details here: Android API Update
@Jeff_Lockhart - thanks for chiming in. While it is true that there are differences in locking behavior, it is extremely unlikely that they have anything to do with what the OP is reporting. Given the use of a single select statement executing under controlled testing conditions and considering the similarity in timings between the keyed and non-keyed execution, the most likely issue is that the “non-key” test is actually resulting in an encrypted database unintentionally.
Is there anything we can do to help with the profiling?
Should we focus on the android-sqlcipher library or do we need to look at the sqlcipher library?
Hello @Lupus. We’re still looking into this and don’t have an update for you right now. One further test to consider was recommended by @developernotes, i.e.
try timing your query through the standard SQLCipher for Android client library for comparison
This would help isolate whether the issue is being introduced by the Support API wrapper layer or the underlying SQLCipher for Android API. Is this something you’d be willing to experiment with in your test application?
We have solved the issue.
We got many calls to getnstimeofday64 that we could not find a reason for.
Turns out we mixed debug and release while building the android-database-sqlcipher library, so we got many calls to LOG_WINDOW that causes calls to getnstimeofday64.
We build the android-database-sqlcipher library once as debug target and once as release target. The release variant used the debug build of the sqcipher, openssl libraries for linking.
After we did a clean checkout, init, build-release we got the correct android-database-sqlcipher.
We could not find the reason for the incorrect linkage, but with the clean checkout workaround we now know how to handle that.
The performance is quite a bit better than the android native sql engine.
Sorry the trouble we have caused and many thanks for your help.