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)
_connection.Open();
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:
SqliteConnection.SetConfig(SQLiteConfig.Serialized);
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:
journal_mode=WAL
cache_size=1
synchronous=1
locking_mode=EXCLUSIVE
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)
{
try
{
databaseCommand.Connection = connection;
databaseCommand.Transaction = transaction;
await databaseCommand.ExecuteNonQueryAsync();
}
finally
{
databaseCommand.Dispose();
}
}
transaction.Commit();
}
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.
CREATE TABLE Foo (
ID TEXT PRIMARY KEY
);
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))
});
Conclusion
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.
