Performance issue with FMDB and SQLCipher

[Arie]

Hi,

I'm developing an app which has a database with info that need to be protected. The largest table of this database has about 65000 rows of data. I started development with an unencrypted database, but now I'm implementing the encrypted version. I use FMDB as a database wrapper and I got SQLCipher together with it from CocoaPods.

The app was running fine until I used the encrypted database. Running on an iPhone4, it took about 3 seconds to query and render (OK, that was still annoying, but the cause of that was rendering, not the queries), but using the encrypted database, it can take up to 20 seconds. I used Instruments to search the method which was so time consuming. It appears to be sqlcipher_cc_kdf , called from the FMDB query method.

I search for solutions, but wasn't able to find anything. I tried to increase the page size, setting it in my converting script directly after the ATTACH statement and setting it every time after calling ["FMDatabase" open], but that results in an error stating the database is encrypted (really ;-) ) or not a database file.

I really don't know how to proceed, so any help would be highly appreciated! If you need more info, I'll gladly provide it!

Kind regards,

Arie

[Nick Parker]

Hello Arie,

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
* 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.

Finally, to diagnose further the performance of your specific query
statements, could you run an explain query plan [1] command against some
of your queries? The output of the explain query command is described
here [2]. We would be glad to help if you have any further questions
about your results. Thanks!

[1] http://www.sqlite.org/lang_explain.html
[2] http://www.sqlite.org/eqp.html

> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "SQLCipher Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlcipher+...@googlegroups.com
> <mailto:sqlcipher+...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

--
Nick Parker

[Arie Pieter Cammeraat]

Hello Nick,

Thank you for your quick and useful reply! Some of your advises are already implemented, others are on my list now...

I experimented with indexing but it is still unbelievably slow. I came across a perfect example. I have a table with two rows. A simple query to find the appropriate row (searching for an exact language code string, like 'en') takes 900 milliseconds. Just the query. Not opening the connection etc.

EXPLAIN QUERY PLAN outputs that it is using a covering index which consist only of that column.

I don't get why this would take about a second. And this is exemplary.

Thanks in advance!

Arie

[Stephen Lombardo]

Hello Arie,

Are you executing that query as the first operation on a newly opened database? If so, you are not getting an accurate time for the query duration. SQLCipher performs key derivation the first time that the database is accessed. Thus, the first query after open will always take much longer. Note that this is to be expected, as key derivation is a purposefully slow process.

If this is the case, try executing a query like "SELECT count(*) FROM sqlite_master;" immediately after opening the database. The timing for that query will be roughly indicative of the KDF performance. Then, execute your query to see what the performance looks like.

Cheers,
Stephen

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.

[Arie Pieter Cammeraat]

Hello guys,

Thank you for your incredible quick replies. I cannot express how I appreciate that!

I didn't know the key derivation is performed at the first query. It seems that solving this will make my app behave as it should.

But, after digging forums I don't know what is the best practice. Since almost every viewController in my app takes data from the DB, I would prefer to keep the connection open all the time. But it seems like this isn't a good practice because a crash could result in a crippled DB file. And since users can modify the factory default data, I cannot say: just reinstall the app again.

Do you have any best practice tips for me?

Thanks a lot!

Arie

> You received this message because you are subscribed to a topic in the Google Groups "SQLCipher Users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlcipher/d-j8eRjc7Qg/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlcipher+...@googlegroups.com.

[William Gray]

Hi Arie,

If you do manage to corrupt a SQLCipher (or even SQLite) database with a crash I'd be pretty impressed, it's quite difficult! If you're worried about leaving the database in an inconsistent state because there was a crash after some statements have been executed and others have not try wrapping your statements in a transaction, catching any exceptions and executing a rollback before re-throwing the exception. FMDB has support for transactions in the API. Also since you're using FMDB you might make use of FMDatabaseQueue to ensure that all commands to the database are serialized over one dispatch queue, and share that queue object in some global way (e.g. create a singleton or keep a reference to it on the database delegate) and then have your view controllers use the queue object to interact with the db. By using a shared queue object you don't have to be so concerned about hitting the database from code that's not executing on the main thread.

Hope that helps!
Billy Gray

> To unsubscribe from this group and stop receiving emails from it, send an email tosqlcipher...@googlegroups.com.

[William Gray]

You might also ask Gus about this over on the FMDB list, he may have better advice in terms of best practices with an iOS app since he's the fellow who wrote FMDB and I'm simply a fan of the library, I'm not currently using it in production for anything (I will for any new projects, but for now I have my own tortured API for this.) What To Do may be different than normal since with SQLCipher you ideally don't want to open and close the db connection every time you hit the db due to key-derivation.

B