iOS SQLite Slow Performance

Question

I am using SQLite in my iOS app and I have a lot of saving/loading to do while the user is interacting with the UI. This is a problem as it makes the UI very jittery and slow.

I've tried doing the operations in an additional thread but I don't think this is possible in SQLite. I get the error codes SQLITE_BUSY and SQLITE_LOCKED frequently if I do that.

Is there a way to do this in multithreading without those error codes, or should I abandon SQLite?

Answer 1

It's perfectly possible, you just need to serialise the access to SQLite in your background thread.

My answer on this recent question should point you in the right direction I think.

As mentioned elsewhere, SQLite is fine for concurrent reads, but locks at the database level for writes. That means if you're reading and writing in different threads, you'll get SQLITE_BUSY and SQLITE_LOCKED errors.

The most basic way to avoid this is to serialise all DB access (reads and writes) either in a dispatch queue or an NSOperationQueue that has a concurrency of 1. As this access is not taking place on the main thread, your UI will not be impacted.

This will obviously stop reads and writes overlapping, but it will also stop simultaneous reads. It's not clear whether that's a performance hit that you can take or not.

To initialise a queue as described above:

NSOperationQueue *backgroundQueue = [[NSOperationQueue alloc] init];

[backgroundQueue setMaxConcurrentOperationCount:1];

Then you can just add operations to the queue as you see fit.

Answer 2

Having everything in a dedicated SQLite thread, or a one-op-at-a-time operation queue are great solutions, especially to solve your jittery UI. Another technique (which may not help the jitters) is to spot those error codes, and simply loop, retrying the update until you get a successful return code.

Answer 3

Put SQLite into WAL mode. Then reads won't be blocked. Not so writes - you need to serialize them. There are various ways how to achieve it. One of them is offered by SQLite - WAL hook can be used to signal that the next write can start.

WAL mode should generally improve performance of your app. Most things will be a bit faster. Reads won't be blocked at all. Only large transactions (several MB) will slow down. Generally nothing dramatic.