CODEWITHSUNDEEP
objective-ciosmultithreadingperformancesqlite
VTS12
VTS12

Reputation: 452

iOS SQLite Slow Performance

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?

Upvotes: 5

Views: 6190

Answers (6)

Jan Slodicka
Jan Slodicka

Reputation: 1515

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.

Upvotes: 2

paulbailey
paulbailey

Reputation: 5346

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.

Upvotes: 3

Graham Perks
Graham Perks

Reputation: 23390

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.

Upvotes: 2

CarlJ
CarlJ

Reputation: 9481

OFF:

Have you checkout: FMDB it is a sqlite Wrapper and is thread safe. I used it in all my sqlite Project.

Upvotes: 0

Jeremy
Jeremy

Reputation: 9030

I recommend using Core Data which sits on top of sqlite. I use it in a multithreaded environment. Here's a guide on Concurrency with Core Data.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

Don't abandon SQLite. You can definitely do it in a thread different than the UI thread to avoid slowness. Just make sure only one thread is accessing the database at a time. SQLite is not great when dealing with concurrent access.

Upvotes: 0

Related Questions