5

I used SQLCipher to encrypt sqlite database in my app.everything is ok but my app runs slow during fetching database.i changed PRAGMA kdf_iter to 4000 and it's still slow.before encryption i don't have any problem.

-(NSError *) openDatabase {

    NSError *error = nil; 
    NSString *databasePath = [self getDatabasePath];

    const char *dbpath = [databasePath UTF8String];   
    int result = sqlite3_open_v2 (dbpath, &db , SQLITE_OPEN_READWRITE , NULL);
    if (result == SQLITE_OK) {

        sqlite3_exec(db, [@"PRAGMA kdf_iter = '4000';" UTF8String], NULL, NULL, NULL);
        sqlite3_exec(db, [@"PRAGMA key = 'password'" UTF8String], NULL, NULL, NULL);

        NSLog(@"Password is correct , Database is Activated");
        sqlite3_exec(db, [@"PRAGMA cipher = 'aes-256-cfb';" UTF8String], NULL, NULL, NULL);

    }
    else {
        NSLog(@"Incorrect password!");
    }
    if (result != SQLITE_OK) {
        const char *errorMsg = sqlite3_errmsg(db);
        NSString *errorStr = [NSString stringWithFormat:@"The database could not be opened: %@",[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]];
        error = [self createDBErrorWithDescription:errorStr andCode:kDBFailAtOpen];

    }

    return error;
}
Nilesh
  • 701
  • 5
  • 14
iSibDev
  • 340
  • 5
  • 18

2 Answers2

4

Finally i could optimize my SQLCipher performance with Nick Parker useful guidance.

As he said:

There are a few very important guidelines for optimal SQLCipher performance:

  • Do not repeatedly open and close connections, as key derivation is very expensive, by design. Frequent opening / closing of the database connection (e.g. for every query) is a very common cause of performance issues that can usually be easily resolved using a singleton database connection.
  • Use transactions to wrap insert / update / delete operations. Unless executed in a transaction scope, every operation will occur within it's own transaction which slows things down by several orders of magnitude
  • Ensure your data is normalized (i.e., using good practices for separation of data into multiple tables to eliminate redundancy). Unnecessary duplication of data leads to database bloat, which means more pages for SQLCipher to operate on
  • Ensure that any columns that are used for searches or join conditions are indexed. If you don't, SQLCipher will need to execute full database scans across large numbers of pages
  • Vacuum periodically to ensure databases are compact if you do large deletes, updates etc.

To diagnose performance problems with specific query statements, it may be helpful to run an explain query plan command against specific queries.

If you are uncertain of what queries are performing poorly, SQLCipher includes a pragma called cipher_profile that allows for profiling queries and their respective execution time in milliseconds.

This is the Reference Link

Many thanks to Nick Parker.

Also this blog was very useful for me.

iSibDev
  • 340
  • 5
  • 18
0

Why are you using CFB mode (aes-256-cfb) instead of CBC?

I don't believe hardware encryption supports AES CFB so it probably will use software encryption which can easily be 500+ times slower.

From the docs:

SQLCipher uses aes-256-cbc as the default cipher and mode of operation. It is possible to change this, though not generally recommended

zaph
  • 111,848
  • 21
  • 189
  • 228