SQLCipher for Android was getting slow

SQLCipher
1

SQLCipher for Android was getting slow when I did following.
insert->update->delete(with DROP TABLE)->insert->…(loop)

Any suggestions?

2

Are you running all of the operations in a transaction? If not you will want to do so.

Please also consult the guide on SQLCipher Performance to ensure that your application is adhering to the guidelines optimal performance.

3

Yes, I’m running all of the operations in a transaction.

Thank you for your information. I will refer that link and try some solutions.

4

I’m running the following operation.

getWritableDatabase();
beginTransaction();
insert(); (0.1 million times)
setTransactionSuccessful();
endTransaction();
close();
getWritableDatabase();
update(); (0.1 million column)
setTransactionSuccessful();
endTransaction();
close();
getWritableDatabase();
delete(); (All data)
execSQL(“drop table XXX”);
execSQL(“create table XXX”);
close();

I’ve done 30 times the above. Then, update() time is getting slow.

first time: 4269ms
10th time: 4627ms
20th time: 4780ms
30th time: 5466ms

In addition, I tried to do similar code using SQLite. Then, update() time wasn’t getting slow.

first time: 2219ms
10th time: 2347ms
20th time: 2020ms

I don’t know what’s wrong.
Any suggestion?

5

@g-oku Based on the test scenario you describe, it is quite possible that you are running into an expanding database due to the repeated inserts, updates, and drops. SQLCipher does not automatically vacuum the database for you. As noted in the final point of the performance post, performing periodic vacuum operations will keep the database compact, particularly when you are performing large deletes, multiple updates etc. Can you try adding an execSQL(“VACUUM”); operation following the table drop, then re-running the test?

6

Thank you! It works fine.

7

@g-oku Excellent, I’m glad to hear that resolved the problem!

8

One more question.
I’ll try to set auto_vacuum “FULL” in addition to invoke VACUUM.
Do you have any concerns about that?
I don’t know why default setting is “NONE” because SQLite for Android is “FULL”.

9

@g-oku We disable auto vacuum by default in all SQLCipher builds for consistency. There is a potential increase in overhead on various operations with auto_vacuum enabled, since the database must be reorganized on updates/deletes to relocate and truncate empty pages. This has some potential for performance impact and, for most database usages, unconstrained growth is very unusual. As a result, we opt to leave it off, and allow the developer to either vacuum when necessary.

Your test of repeatedly wiping and recreating large tables is a notable exception. You can certainly turn it auto_vacuum on though, since your application needs it.

10

Thank you very much for your kind response. I understand that.
I’ll support both VACUUM function and auto_vacuum “FULL” in my application.