Thomas Bandt

Über mich | Kontakt | Archiv

Performance Optimization of SQLite on iOS with Xamarin

For storing data locally within an iOS app there is almost no alternative to SQLite. Wherever you look, everyone seems to recommend that little but powerful database system.

So I started with it as well. I had only a dozen tables and a few hundred records to store. As my application works completely offline only synchronizing with a server every minute, there are a lot more of reads than writes.

I didn't think that much about performance - iPhones are no super computers, that's for sure, but a few hundred records should not be a problem.

As it turned out, it was a problem. A huge problem.

Fetching my data with ADO.NET (Mono.Data.Sqlite) was very time consuming, which means that simple selects of a few dozen records could last 200-600 milliseconds. What sounds fast at the first glance is a real show-stopper when you're swiping through a set of pictures and every swipe delays about half a second or more, which always feels like 1-2 seconds to a user.

I tried literally everything I found online, not everything had an effect. At the end some things in combination did the trick. Today I am not talking about 200-600 milliseconds for that kind of selects mentioned above, but about 10-20 milliseconds.

Read on to find all the "magic".

Use a single connection for your whole application

SQLite is a file based database system. That means that every connection is trying to get exclusive access to that file (depending on the level of protection against data-loss you have configured via pragma-options).

Using a single connection solves that problem, and it's not expensive in any way as your app is the only client, that is accessing the database.

A static helper like this could help:

public static class DbConnection
    private static SqliteConnection _connection;

    public static SqliteConnection GetConnection()
        if (_connection == null)
            _connection = new SqliteConnection("...");

        if (_connection.State == ConnectionState.Closed)

        return _connection;

Use Serialized Threading Mode

As you're using a single connection across different threads sooner or later, set Serialized as the threading mode before any connection is opened in the FinishedLaunching()-method of your AppDelegate:


Set PRAGMA Options

To be honest, the following options are the result of trial & error. I am no database specialist so it could be possible there is something set which is useless, or something is still missing.

What I have set:





Use Transactions

Using transactions for any kind of write operation (UPDATE, DELETE, INSERT) improved the performance dramatically, all the more when using multiple of these operations at one go.

SqliteConnection connection = DbConnection.GetConnection();

using (var transaction = connection.BeginTransaction())
    foreach (SqliteCommand databaseCommand in _databaseCommands)
            databaseCommand.Connection = connection;
            databaseCommand.Transaction = transaction;

            await databaseCommand.ExecuteNonQueryAsync();


I am using the unit of work pattern, so I collect all the operations as commands in _databaseCommands, and commit them at once.

Use async await

The SqliteCommand offers async alternatives to ExecuteNonQuery(), ExecuteScalar() and ExecuteReader(). Just use it! But make sure you have some time to change your codebase, as that will trigger to the top of every single call at the UI of your application. But at the end of the day you will be able to access your database without blocking your UI thread. That's just awesome.

Don't use SELECT * FROM

Just don't use it. Instead write out every single column you want to select.

Order the tables within your joins

You can learn a lot of optimizing your queries for the SQLite Query Planner. But from my experience the most important point is to organize your joins the right way, as described in the document.

To make it short: put the table you're getting more results from on the left.

Make use of primary keys

Use a primary key for every single table, more than ever when you're joining that table in your queries.

And by the way: if you're using Guids as identifiers, you can store them as text and make them a primary key, too. I didn't find any serious performance loss with it.


Index columns used in where clauses or joins

Create single-column indexes as well as composite indexes for columns you frequently use as filters.

Mapping: Access columns in your data reader by ordinal, not by column name

Even if your database is accessed very fast, one single piece is left: the correct mapping.

I used to be happy with just accessing the columns by their name. As it turned out, that is a really expensive way. Significantly cheaper is the access by ordinal.

So for

SELECT ID, FirstName, LastName FROM Users

The normal way would be

var users = new List();

using (SqliteCommand command = DbConnection.GetConnection().CreateCommand())
    command.CommandText = "SELECT ID, FirstName, LastName FROM Users";
	command.CommandType = CommandType.Text;

    using (DbDataReader reader = await command.ExecuteReaderAsync())
		while (reader.Read())
			users.Add(new User
				ID = Guid.Parse(reader["ID"].ToString()),
				FirstName = reader["FirstName"].ToString()),
				LastName = reader["LastName"].ToString())

return users;

And the better way is

users.Add(new User
	ID = Guid.Parse(reader.GetString(0)),
	FirstName = reader.GetString(1)),
	LastName = reader.GetString(2))


It's important to know what you are doing here more in detail than it is today in server or desktop environments, where SQL Server for example delivers so much power and the hardware is so fast, that simple tasks like my one are not worth to consider that much optimizations.

But as mobile devices even in 2014 are not that fast as their desktop or server computing counterparts, it is good to know even the details.


  1. LaurieP schrieb am Montag, 5. Mai 2014 04:52:00 Uhr:

    Thomas, thanks for the great tips.
  2. Ryan Alford schrieb am Montag, 5. Mai 2014 21:10:00 Uhr:

    I would suggest that you look into the sqlite-net ORM. It's lightning fast, and will cut down on the amount of code you have to write to get data from a SQLite database.

    Use it, and I can almost guarantee your performance issues will no longer be with database access.
  3. Thomas schrieb am Montag, 5. Mai 2014 21:32:00 Uhr:

    I don't have any performance issues anymore ;-)

    sqlite-net is a nice library for simple tasks, but it has not been an option for that project, as I partially have more complex data types and not only POCO's.

« Zurück  |  Weiter »