Inserts are very slow on android 7.x with sqlcipher 4.0.1

[mpelc87]

Hi,

I have an issue with performance of sqlcipher 4.0.1 on Android 7.x
Inserts of big amounts of records in one transactions are ~10 times slower on sqlcipher 4.0.1 then on 3.5.9

Here is my sample app I'm using to measure performance:

`class MainActivity : AppCompatActivity() {
var database: SQLiteDatabase? = null

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.activity_main)

    initSqlCipher()
    createSchema()
    insert()
}

fun initSqlCipher() {
    SQLiteDatabase.loadLibs(this)
    val databaseFile = getDatabasePath("demo.db")
    databaseFile.mkdirs()
    databaseFile.delete()

    database = SQLiteDatabase.openOrCreateDatabase(databaseFile, "test123", null)
}

private fun insert() {
    val startTime= System.currentTimeMillis()

    database?.execSQL("begin;")
    (1..10000).forEach {
        database?.execSQL("insert into some_table(name, surname) values(?, ?)",
            arrayOf<Any>("one for the money", "two for the show"))

    }
    database?.execSQL("commit;")

    Log.e("SchemaLog", "Inserted in: ${System.currentTimeMillis() - startTime} ms")
}

fun createSchema() {
    val startTime= System.currentTimeMillis()

    database?.execSQL("""CREATE TABLE some_table (
            name TEXT,
            surname TEXT)""".trimIndent())

    Log.e("SchemaLog", "Schema created in: ${System.currentTimeMillis() - startTime} ms")
}

}`

[developernotes]

Hi @mpelc87,

I've integrated your test scenario into the SQLCipher for Android test suite here. I ran the same test using the 3.5.9 library 10 times which averaged 393 milliseconds, whereas the 4.0.1 library averaged 373 milliseconds. Would you try running the test yourself and report your findings?

[raiym]

@mpelc87 which device do you use for tests?
In my case 4.0.1 works fine on Pixel 2, but takes really long time on Nexus 6P on both Android 8.1 and 7.1.2.
Reverted back to 3.5.9

[sjlombardo]

@raiym - can you try updating to SQLCipher 4.0.1 but then set the compatibility settings (https://discuss.zetetic.net/t/upgrading-to-sqlcipher-4/3283) to use SQLCipher 3 defaults, via PRAGMA cipher_compatibility = 3; and let us know the results?

[mpelc87]

@developernotes I run your test today and I had times similar to yours. Also my example worked ok today. Its really weird... It looks like sometimes it works really slow, sometimes its fast in that case.
@raiym I'm using simulator with x86 image of android 7.1.1 and also device samsung galaxy s6 with android 7.0.
Despite that the test and sample code works ok our app still works really slow.
I was debugging it and its using sqLiteStatement.executeInsert() for inserting (not execSQL())
Should I do sqLiteStatement.close(); for cleanup?

@sjlombardo it looks like nice workaround. I will also try that tomorrow.

[developernotes]

Hi @mpelc87

It looks like sometimes it works really slow, sometimes its fast in that case.

Are you experiencing both the slow and fast behavior on the same device/emulator?

[raiym]

@sjlombardo did it with 4.0.1 -- same result. Slower, compared to 3.5.9.

    @Override
    public void postKey(SQLiteDatabase sqLiteDatabase) {
        Log.d(TAG, "Entering postKey()");
        sqLiteDatabase.rawQuery("PRAGMA cipher_compatibility = 3;", new String[]{});
    }

[mpelc87]

@developernotes
I've noticed that performance of inserting (in 4.0.1 version) is oddly related to size of data inserted in the record
I've created other code sample which increases size of data put in record.

Here are results on samsung galaxy s6 android 7.0:

10000 records with payload of length: 296 inserted in: 334 ms
10000 records with payload of length: 297 inserted in: 330 ms
10000 records with payload of length: 298 inserted in: 376 ms
10000 records with payload of length: 299 inserted in: 377 ms
10000 records with payload of length: 300 inserted in: 1197 ms
10000 records with payload of length: 301 inserted in: 1246 ms
10000 records with payload of length: 302 inserted in: 1031 ms
10000 records with payload of length: 303 inserted in: 1396 ms

Here are results on simulator android 7.1.1 x86_64:

10000 records with payload of length: 297 inserted in: 98 ms
10000 records with payload of length: 298 inserted in: 98 ms
10000 records with payload of length: 299 inserted in: 94 ms
10000 records with payload of length: 300 inserted in: 1317 ms
10000 records with payload of length: 301 inserted in: 1260 ms
10000 records with payload of length: 302 inserted in: 1330 ms

You can notice that significant increase in times between 299 and 300 characters

Code sample:

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase database = null;

    StringBuilder payload = new StringBuilder("a");

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        initSqlCipher();
        createSchema();

        Button button = findViewById(R.id.insertButton);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                for(int i=0; i< 1000; ++i) {
                    payload.append("a");
                    insert(payload.toString(), 10000);
                    clearTable();
                }
            }
        });
    }

    private void initSqlCipher() {
        SQLiteDatabase.loadLibs(this);
        File databaseFile = getDatabasePath("demo.db");
        databaseFile.mkdirs();
        databaseFile.delete();

        database = SQLiteDatabase.openOrCreateDatabase(databaseFile, "test123", null);
    }

    private void createSchema() {
        database.execSQL("Create table test(name TEXT)");
    }

    private void insert(String payload, int amountOfRecords) {
        long start = System.currentTimeMillis();
        database.beginTransaction();

        try {
            for (int i=0; i< amountOfRecords; ++i) {
                database.execSQL("insert into test (name) values (?)",
                        new String[] { payload});
            }
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
            Log.e("SqlCipherTest", amountOfRecords + " records with payload of length: " + payload.length() + " inserted in: " + (System.currentTimeMillis() - start) + " ms");
        }
    }

    private void clearTable() {
        database.execSQL("delete from test");
    }
}

[raiym]

@mpelc87 could you be so kind and post same tests for 3.5.9?

[mpelc87]

@raiym On 3.5.9 insert time grows linearly more or less

Here are results of the same tests for 3.5.9
device samsung galaxy s6 android 7.0:

10000 records with payload of length: 296 inserted in: 630 ms
10000 records with payload of length: 297 inserted in: 616 ms
10000 records with payload of length: 298 inserted in: 616 ms
10000 records with payload of length: 299 inserted in: 628 ms
10000 records with payload of length: 300 inserted in: 624 ms
10000 records with payload of length: 301 inserted in: 630 ms
10000 records with payload of length: 302 inserted in: 633 ms
10000 records with payload of length: 303 inserted in: 620 ms
10000 records with payload of length: 304 inserted in: 628 ms

simulator android 7.1.1 x86_64:

10000 records with payload of length: 296 inserted in: 399 ms
10000 records with payload of length: 297 inserted in: 395 ms
10000 records with payload of length: 298 inserted in: 394 ms
10000 records with payload of length: 299 inserted in: 407 ms
10000 records with payload of length: 300 inserted in: 386 ms
10000 records with payload of length: 301 inserted in: 437 ms
10000 records with payload of length: 302 inserted in: 396 ms
10000 records with payload of length: 303 inserted in: 396 ms
10000 records with payload of length: 304 inserted in: 389 ms
10000 records with payload of length: 305 inserted in: 389 ms

[raiym]

@mpelc87 thank you very much.
It helps a lot

[TheNephilim88]

The issue is not limited to inserts. Same issue happens for SELECTS.

3.5.9 is about 10x faster in SELECTing many data.

Even with compatibility mode or manually set cipher defaults to the ones on v3.x

(Did test with Nexus 6P)

[TheNephilim88]

Okay, not 10 times fast, but here are my results comparing v3.5.9 vs v4.0.1 (same operations on same dataset, just exchanged the SQLCipher version)

v4.0.1: 13551 ms
v3.5.9: 2319ms

[developernotes]

Hi @TheNephilim88

Thanks for offering up a timing report. It would be helpful to understand the size of the database, the exact query you were performing, and how much data was being retrieved. Were you timing just the cursor retrieval of the data? Would you mind posting some additional details about your scenario?

[developernotes]

Hi @mpelc87

Would you try disabling the memory wiping feature that was added in 4.0.0 and run your scenario(s) again? You can do this by executing the following command:

database.execSQL("PRAGMA cipher_memory_security = OFF;");

We look forward to hearing your results.

[TheNephilim88]

@developernotes
Sure, my data are separated into multiple tables. When loading those data (yes I measured only data reading/cursor retrieval) I do load all at once, but have multiple queries per entry in my main table as further information for them are in referenced via IDs to other tables.
So with reading those data there is a large amount of SELECTs on different tables again and again.

With your question I started investigating and optimizing my queries as they are already quite old and not that intelligent as I would do them today 🤣

So here are my new measurements, I did multiple runs and added avg here:
App with following settings:
SQLCipher 3.5.9, unoptimized queries: 2702ms
SQLCipher 4.0.1, unoptimized queries: 13136ms
SQLCipher 4.0.1, unoptimized queries, with cipher_memory_security = off: 3584ms

SQLCipher 3.5.9, optimized queries: 1312ms
SQLCipher 4.0.1, optimized queries: 6635ms
SQLCipher 4.0.1, optimized queries, with cipher_memory_security = off: 1583ms

So cipher_memory_security seems to have a huge performance impact. Was it available on 3.5.9 before? Is it safe to turn it off?