Use Akavache for faster Sqlite3

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.

Key-Value Store

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.

Creates Transactions

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.

Condense Read/Writes

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

Microsoft MVP | Xamarin MVP | Xamarin Certified Developer |
Exrin MVVM Framework | Xamarin Forms Developer | Melbourne, Australia

Related Posts

2 Comments

  1. Emil

    Thanks for this great article. “if your data is just cache and it doesn’t matter if you lose the data”- by that do you mean if we are synchronizing with the server. so if our sqlite db is offline cache of a server?

    for XF there are 2 libraries SQLite.Net.Async and SQLite.Net. Does it even make sense to use
    below as libraries are already dedicated?

    Connection.Execute(“PRAGMA synchronous=OFF”);

    What does this command below do? Does it cache entire DB into memory?does it expire automatically when app is terminated?
    Connection.Execute(“PRAGMA temp_store=MEMORY”);

    1. Adam Pedley

      Yes, by that line I mean if the sqlite db is just for offline caching.

      Connection.Execute(“PRAGMA temp_store=MEMORY”); Means that it can temporarily store data in memory, while its waiting to write to the flash storage. The only issue with this approach is, if the power on the mobile device is turned off before the write has finished, you will forever lose the data.

Leave A Comment?