SQLCipher performance

#1

Hi there,

I have the following situation. When I run this code (with commenting out “table1” existence query part) cursor.getCount() takes only several hundred milliseconds (728, 824, 639 ms) on SQLCipher 4.0.1. If I remove comments, so table existence query is also executed then cursorExist.getCount() takes only several milliseconds (most of the time only 1ms) but it causes the cursor.getCount() to be executed in about 1 minute (66324, 60798, 68245 ms). I don’t know why. On SQLCipher 3.5.3 everything was OK, so cursor.getCount() always runs fast, but on 3.5.9 it is even slower than on 4.0.1. Note that `table1’ table contains 37,777 lines of rows and 33 columns. Could someone help me to understand why querying table for existence causes the main query to degrade drastically? I even run EXPLAIN QUERY PLAN and EXPLAIN, but does not give any difference between queries.

/*
Cursor cursorExist = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='table1'", new String[]{});
long timeExist = System.currentTimeMillis();
cursorExist.getCount();
IOUtils.closeQuietly(cursorExist);
Log.d("query_performance", "queryAll: " + (System.currentTimeMillis() - timeExist));
*/

Cursor cursor = db.rawQuery("SELECT col1, col2, col3, _id FROM table1 ORDER BY _id", new String[]{});
long time = System.currentTimeMillis();
cursor.getCount();
Log.d("query_performance", "queryAll: " + (System.currentTimeMillis() - time));
#2

Hi @Rustam_Navoyan

What is your observed behavior in the 4 series of SQLCipher for Android (the latest is 4.1.13), when you execute the following command inside the preKey event of a SQLiteDatabaseHook that you provide as an argument when creating your SQLiteDatabase instance:

PRAGMA cipher_memory_security = OFF;
#3

Hi @developernotes

Thank you so much for your reply. After my post, I have found this thread https://github.com/sqlcipher/android-database-sqlcipher/issues/411 where you already said that and tried which solved that performance issue. Also when I changed table columns count from 33 to 4 (or similar small number) it loads fast. Seems that columns count (or otherwise row size) affects on it.
Isn’t it a good idea to execute this command before running my query and then turn that on?

#4

Hi @Rustam_Navoyan

We are happy to hear you were able to resolve the issue.

Are you suggesting toggling the cipher_memory_security option off and then on? It should have no ill effect. If you know you will return a large dataset, you can consider the option of specifying a custom cursor window allocation to see if it improves performance as well. Finally, you may also wish to review our general performance guidance as well.

#5

Hi @developernotes
Yes I meant to toggle it off and then on. But probably I will go with your suggested option above (doing inside the preKey event).