4

I have a sqlcipher DB which is relatively complex (27 tables) but with little entries (between 50-200 entries per table). When I'm running a SELECT statement (same with the corresponding view), joining 3 tables, doing a couple of "LIKE's" (optimized with EXPLAIN), the desktop client CPU (with sqlcipher) takes 3ms for the query.

However the same query on Android with the Cordova-SQLitePlugin takes almost 1900ms - due to the fact opening the database costs about 1800ms, which obviously is repeated after every page load.

The queries are issued like this:

var db = window.sqlitePlugin.openDatabase({name: "myDatabase", key: "mySecret", bgType: 1});
    db.transaction(function(transaction) {
        transaction.executeSql(query, [],function(transaction, result) {
        callback(result);
    }, null);
    },null,null);

Is there anything I can do? Thanks in advance for any hints and clues...

Cheers Chris

Chris
  • 580
  • 5
  • 17

1 Answers1

3

SQLCipher's performance for opening a database is deliberately slow. SQLCipher uses key PBKDF2 to perform key derivation (i.e. thousands of SHA1 operations) to defend against brute force and dictionary attacks. Please see http://sqlcipher.net/design for more information.

The best option is to cache the database connection so that it can be used multiple times without having to open and key the database on each screen. If this is possible, opening the database once during startup is the preferred course of action. Subsequent access on the same database handle will not trigger key derivation, so performance will be quite fast.

If this is not possible the other option is to disable or weaken key derivation. This will cause SQLCipher to use fewer rounds of PBKDF2 when deriving the key. While this will make the database open faster, it is significantly weaker from a security perspective. Thus it is not generally recommended. That said, here is the information on how to reduce the KDF iterations:

http://sqlcipher.net/sqlcipher-api/#kdf_iter

Stephen Lombardo
  • 1,503
  • 8
  • 7
  • Thanks for the good description. I have seen this change but didn't realize what immense performance impact this has. Unfortunately Cordova-SQLitePlugin doesn't keep the DB open - changing the key derivation saves actually about 1400-1800ms...(!!!). I will evaluate the pro's and con's now, thanks again! – Chris Feb 13 '14 at 09:32