Android Question SQLCipher 1.6 and 1.3

[jatko]

In my app I used SQLCipher library v 1.3. When I made the upgrade to 1.6 I saw that the new version is much slower than the old one.
In my app there is many records to synchronize with the server. Inserting 5000 records takes even 2 sekonds more time.

Any idea, how to make it faster?

 

[RB Smissaert]

In my app I used SQLCipher library v 1.3. When I made the upgrade to 1.6 I saw that the new version is much slower than the old one.
In my app there is many records to synchronize with the server. Inserting 5000 records takes even 2 sekonds more time.

Any idea, how to make it faster?

I saw a thread somewhere (not on this forum) where this was the problem:
PRAGMA cipher_memory_security
Turning this off made all the difference:

https://www.zetetic.net/blog/2018/11/30/sqlcipher-400-release/

RBS

 

[RB Smissaert]

I saw a thread somewhere (not on this forum) where this was the problem:
PRAGMA cipher_memory_security
Turning this off made all the difference:

https://www.zetetic.net/blog/2018/11/30/sqlcipher-400-release/

RBS

This is the thread I referred to:

https://github.com/sqlcipher/android-database-sqlcipher/issues/411

RBS

 

[RB Smissaert]

This is the thread I referred to:

https://github.com/sqlcipher/android-database-sqlcipher/issues/411

RBS

I have now tested this after doing away with Spatialite, as I couldn't get that to work with SQLCipher 1.6.
(see this thread:
https://www.b4x.com/android/forum/threads/new-sqlcipher-4-with-spatialite.103768/)

Running this SQL:

pragma cipher_memory_security = OFF

Did indeed make a big difference, and not only on non-select SQL, eg with this SQL:

WITH RECURSIVE input(sud) AS(VALUES('53..7....6..1.5....98....6.8...6...34..8.3..17...2...6.6....2.....4.9..5....8..79')), digits(z, lp) AS(VALUES('1', 1) UNION ALL SELECT CAST(lp + 1 AS TEXT), lp + 1 FROM digits WHERE lp < 9), x(s, ind) AS (SELECT sud, instr(sud, '.') FROM input UNION ALL SELECT substr(s, 1, ind - 1) || z || substr(s, ind + 1), instr(substr(s, 1, ind - 1) || z || substr(s, ind + 1), '.') FROM x, digits AS z WHERE ind > 0 AND NOT EXISTS(SELECT 1 FROM digits AS lp WHERE z.z = substr(s,((ind - 1) / 9) * 9 + lp, 1) OR z.z = substr(s,((ind - 1) %9) +(lp - 1) * 9 + 1, 1) OR z.z = substr(s,(((ind - 1)/3) % 3) * 3 +((ind - 1) / 27) * 27 + lp +((lp - 1) / 3) * 6, 1))) SELECT S as Solution FROM x WHERE ind = 0 order by s asc

Before running the above pragma it took some 7 seconds and after running that pragma some 0.66 seconds.
This is on a Samsung S9, encrypted database with SQLCipher 1.6.

RBS

 

[RB Smissaert]

In my app I used SQLCipher library v 1.3. When I made the upgrade to 1.6 I saw that the new version is much slower than the old one.
In my app there is many records to synchronize with the server. Inserting 5000 records takes even 2 sekonds more time.

Any idea, how to make it faster?

Did you try my suggestion?
I have this an optional setting now in my app, but always have it turned off (pragma cipher_memory_security = OFF) as it makes a big difference.

RBS

 

[jatko]

I started some tests. On my device it works. But it's not so easy to migrate database for some hundred users. It takes much time. So I thank You very much for your help.

 

[RB Smissaert]

I started some tests. On my device it works. But it's not so easy to migrate database for some hundred users. It takes much time. So I thank You very much for your help.

What do you mean with migrate database? Can't your users run any SQL on startup?

RBS

 

[jatko]

In my app I used SQLCipher 1.3. When I want to upgrade to 1.6 I can't use the same file *.sqlite only with replace library version.
I decided to wait a little. Next month I will have the momen, when I can delete every old data in my app. The users will synchronize their devices with the server and in that time I will delete database in 1.3 version and create new in 1.6 version.