When Should I Do Certain Sqlite Operations on Another Thread(Not The Main Thread)

When should I do certain SQLite operations on another thread(not the main thread)?

General rule for everything: If it's fast enough, do it on the main thread. If not, use a worker thread.

Unless you have a ridiculously huge database, a single operation almost never warrants a separate thread. Databases in general are designed to scale well, but of course a very big database (10,000+ rows?) will be a bit slower than a small one. 30 rows, however, is nothing.

I would start threading stuff if you have a lot of operations going on, like a bunch of queries, or complicated queries that span several tables.

As with everything - profile your app, and if it's too slow, optimize. Don't write an awesome synchronized super-duper multi-core-ready database handler if none of your queries takes longer than 2ms.

Should I always perform database operation in a separate thread in android?

Exactly, you should. If you use Room, it will help you greatly with that as it support livedata/flow, and you can just watch from your fragments, and it will not perform database operation in the main thread.

what is room:
https://developer.android.com/training/data-storage/room

Google codelab to help you get going:
https://developer.android.com/codelabs/android-room-with-a-view-kotlin#0

Android database - which thread to use

You can use the main thread, just make sure that your data size won't increase in the future, or you'll have to move to an AsyncTask or similar implemenation.

Edit:

Just to clarify, it's bad practice to do any database access on the main thread, but it is not forbidden.

sqlite and threading with iPhone SDK

I solved this problem by using one thread and an NSOperationQueue to insert the Data. I would give it some thought. I've never been able to get a stable System with mutliple threads, and most writes aren't that important that queuing really helps.

As per request, some more Infos:

I have a subclass of NSOperation that I instantiate with the model object I want to store.
These operations are than submitted to an extension of NSOperationsQueue that runs in a seperate thread. This custom Queue just adds a pointer to the database instance. When the operation is executed, it uses the [NSOperationsQueue currentQueue] property to access the queue and than the database. On purpose, i used non-concurrent operations (maxOperations was set to 1)

Hence, only one query (or update) is executed at a time consecutivly, completely in the background.

Obviously you need some kind of callback after you're finished.

It is possibly not the fast, but the most stable and cleanest solution i could find.

Docs:
http://developer.apple.com/library/ios/documentation/General/Conceptual/ConcurrencyProgrammingGuide/OperationObjects/OperationObjects.html

http://www.cimgf.com/2008/02/16/cocoa-tutorial-nsoperation-and-nsoperationqueue/

http://icodeblog.com/2010/03/04/iphone-coding-turbo-charging-your-apps-with-nsoperation/

sqlite concurrency issues

I am in the process of writing a program in Objective-C that is nearly identical w.r.t behavior.

Here is how I intend to synchronize access (The question I asked there is kind of unrelated, but have a look at the code):

Calling sqlite3_close for a static sqlite3* handle

I am going to use a static NSLock instance and lock it while writing, and then unlock it when I'm done.

I don't know how much of a change that will be for your application, but it might be a solution.

---

Related Topics