SqlCipher 4.2 on Android performance issues even without encryption

[Lupus]

Hi,

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.

There is already an post on stackoverflow regarding this issue: https://stackoverflow.com/questions/58954634/sqlcipher-4-2-is-300-solwer-even-if-encryption-is-not-active-android

• 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:

repositories {
    flatDir {
        dirs 'libs/aars'
    }
}

dependencies {
    ...
    implementation (name:'android-database-sqlcipher-4.2.0-release', ext:'aar')
    ...
    }

this is how we open the database:

public SupportSQLiteOpenHelper getDatabase(final DatabaseIdentifier identifier, final DatabaseDefinition databaseDefinition) throws IOException, GeneralSecurityException {
    SupportSQLiteOpenHelper.Factory factory;
    final int sqliteEngine = databaseDefinition.getSqliteEngine();
    if (sqliteEngine == DatabaseDefinition.SQLITE_ENGINE_ANDROID_FRAMEWORK) {
        factory = new FrameworkSQLiteOpenHelperFactory();
    }
    else if (sqliteEngine == DatabaseDefinition.SQLITE_ENGINE_SQLCIPHER) {
        final SQLiteDatabaseHook sqlCipherHook = new SQLiteDatabaseHook() {
            @Override
            public void preKey(final net.sqlcipher.database.SQLiteDatabase sqLiteDatabase) {
                
            }

            @Override
            public void postKey(final net.sqlcipher.database.SQLiteDatabase sqLiteDatabase) {                
                    sqLiteDatabase.rawExecSQL("PRAGMA cipher_memory_security = OFF;");
            }
        };

        if (databaseDefinition.isSqlCipherEncrypted()) {
            factory = new SupportFactory(databaseDefinition.getSqlCipherEncryptionKey(), sqlCipherHook);
        }
        else {
            factory = new SupportFactory(new byte[0], sqlCipherHook);
        }
    }
    else {
        throw new AssertionError();
    }

    final SupportSQLiteOpenHelper.Callback callback = new SupportSQLiteOpenHelper.Callback(1) {
        @Override
        public void onCreate(final SupportSQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(final SupportSQLiteDatabase db, final int oldVersion, final int newVersion) {
        }

        @Override
        public void onOpen(final SupportSQLiteDatabase db) {
            super.onOpen(db);
            ....
        }
    };

    final SupportSQLiteOpenHelper.Configuration configuration = SupportSQLiteOpenHelper.Configuration
            .builder(mApplication)
            .name(databaseDefinition.getPath())
            .callback(callback)
            .build();
    return factory.create(configuration);
}

[developernotes]

Hi @Lupus

@Lupus

For example this query:
SELECT id FROM tableTest;
takes about 8600 ms to finish

Is this the execution using the system SQLite, or SQLCipher for Android library? What are your timings from the opposite?

Also, what are your results from the general SQLCipher Performance Guidance here, specifically the PRAGMA cipher_profile results:

SQLCipher Performance Optimization

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 w…

[Lupus]

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

[developernotes]

Hi @Lupus

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!

[Jeff_Lockhart]

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

[sjlombardo]

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

[Lupus]

Hi,
we are thankful that this thread get so much attention, really appreciate it.

I don’t think that this is an concurrency related problem, anyhow i will see if i can rule that out completely.

I will try to come up with an complete sample android app, that shows the three different usages of the database.

Thanks!

[Lupus]

This is the sample App i created:

SqlCipherPerfTest.zip

[Lupus]

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?

What infomation should we provide?

Thanks!

[sjlombardo]

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?

[Lupus]

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.

[sjlombardo]

Hello @Lupus thanks for the update, glad to know that you found the root cause and it was not SQLCipher itself.