Akavache is a library built on top of the SQLite3 library but performs faster that SQLite3 library. I just watched the awesome evolve session from Paul Betts about how he wrote a library on top of SQLite3 that helps stop even well seasoned developers from doing a lot of bad things. I was certainly guilty of some.
If you want the TL;DR; Akavache has a lot of smarts in handling multiple threads and condensing or removing superfluous reads or writes and allowing your async app to work efficiently with a single disk store. Paul mentioned that many apps may receive a 2x boost in performance.
First up I want to point out that Avakache is a key-value store, meaning (from what I can see) you place a key and a byte as a value to the store, unlike SQLite where you can have different columns, this one you serialize your object into a byte array then place it in the store. This does limit your ability to search tables based on anything else other than the key.
This isn’t a problem for something like an image cache but may pose problems for more complex scenarios. If you need more complex scenario’s please read on. It will still show you how to make your SQLite performance faster.
SQLite requires that everything be wrapped in a transaction. If you don’t put it in a transaction, it creates one anyway and this takes time. As such Akavache puts everything in a transaction and will take multiple items in the queue into one transaction if possible to speed up times. However if one of those commands does fail it does cause the others to fail. It is a trade off for occasional exceptions.
Single Threaded Access
There is only 1 SQLite file and when you read or write something to the SQLite it places a file lock on it. Nothing can read or write until the previous transaction is completed. Managing multiple threads throughout the application results in a lot of work, hence Akavache handles this for you, placing requests into a queue and handling them one at a time. This will save a lot of time waiting for threads and possible context switching.
Because your mobile app will most likely be multi-threaded you may make calls to the database multiple times from different threads. But this can be wasteful if you look at this scenario.
[Write Foo] [Read Bar] [Read Foo] [Write Bar] [Delete Bar]
As you can see we could easily condense this as
[Write Foo] [Send value back on read (no need to read the DB again, we just wrote, we know the value)] [Read Bar] [Delete Bar] (don't bother writing its just going to be deleted)
If you remember all these commands coming in from different threads you find it easy to see that there may be many superfluous reads and writes.
Make SQLite Less Conservative
SQLite is very conservative making sure that you don’t corrupt your database but this comes at the expense of speed. However if your data is just cache and it doesn’t matter if you lose the data, then you can setup SQLite to be a little less conservative but using these commands.
Connection.ExecuteScalar<int>("PRAGMA journal_mode=WAL"); Connection.Execute("PRAGMA temp_store=MEMORY"); Connection.Execute("PRAGMA synchronous=OFF");
Everything is fairly self explanatory except the journal_mode of WAL. If you want to know more about the advantages and disadvantages of WAL look at the documentation Write-Ahead-Logging