Android Question SQLCipher performance?

[jtare]

I just moved from standard SQL to SQLCipher to protect user data, but I noticed a huge increase in query time, literally it can take up to 3-5 times more than what it took the same query with a non encrypted database, some times a few seconds, 1+ seconds of UI frozen is not aceptable in 2017, almost 2018.

• I populated this encrypted DB with data stored in the non encrypted DB, one by one. Took several minutes.

• The size(mb) of the encrypted db is larger, as far as I know this is ok.

• My database have 10000 entries and growing.

This extra time that it takes to query data makes significantly performance issue.
Someone knows what could be wrong? Or is common this delay in encrypted DB? And what can be done to compensate the delay?

Thanks

EDIT: I show the database entries in a ULV (UltimateListView) which add up half a second of delay (only with encrypted db, don't know why)

 

[Widget]

Have you vacuumed the database recently? Because Sqlcypher doesn't automatically do it. If you are dropping tables regularly or deleting a lot of rows, vacuuming will speed things up.

Does your query have a Where clause and are the columns indexed? Otherwise it has to do a table scan when searching for a non-indexed column which will be quite slow for Sqlcipher because it has to decrypt every record. You can use Explain to see what it is doing compared to the same table when it is not encrypted.

These links may help.
https://www.zetetic.net/blog/2011/5/7/sqlcipher-performance-and-sqlcipherspeed.html
https://stackoverflow.com/questions/33475916/how-to-optimize-sqlcipher-performance
https://stackoverflow.com/questions/19730918/android-sqlcipher-performance-issue

 

[Widget]

One other thing I forgot to mention. If your query has a sort, remove it to see if it speeds things up. You may find it faster to sort the rows using ULV instead of using SQL.

 

[jtare]

Have you vacuumed the database recently?

Yes I did

Does your query have a Where clause and are the columns indexed?

Yes I use a Where clause in most of the queries . I wasn't aware of indexed and non-indexed columns, can this be implemented after the db is created?
For what I read in the link you posted, it seems to be the solution.

If your query has a sort, remove it to see if it speeds things up

It doesn't really speed up.

Until now, I manage to kept the times around 1-1.5 seconds, but it is still a lot of time with a frozen UI.
I will try indexing the column and see if it fix the issue, thanks for the help, I appreciate it.

 

[jtare]

I followed this guide http://www.sqlitetutorial.net/sqlite-index/ and indexed some columns, the most time consuming query was 1.2 sec and now takes half that time.
Other queries went from 500ms to 40ms!

Indexing the columns was the solution, thank you very much.

Even though I'm still interested in shortening the times, I'll keep looking

 

[Widget]

I followed this guide http://www.sqlitetutorial.net/sqlite-index/ and indexed some columns, the most time consuming query was 1.2 sec and now takes half that time.
Other queries went from 500ms to 40ms!

Indexing the columns was the solution, thank you very much.

Even though I'm still interested in shortening the times, I'll keep looking

I'm glad it worked out. There is a Windows program called SQLiteStudio from https://sqlitestudio.pl/index.rvt that will let you create a SQLite database that is encrypted with SQLCipher. (I assume its version of SQLCipher is compatible with B4A.) I use it to create regular unencrypted SQLite databases and just copy it to the B4A project's Files directory (as a .jpg file instead of .db3 so Android doesn't try to compress the file). I find it easier to experiment with SQLite on Windows with SQLiteStudio before I run it on the Android device. Their free version is quite capable.

Use the Explain Query Plan to see if your SQL statement can be optimized. See https://www.sqlite.org/eqp.html. There is also a GUI version of Explain Query Plan that can be found here http://www.visophyte.org/blog/2010/...-explaination-visualizations-using-systemtap/ . I haven't tried it but it looks interesting.