It is ok to make any database operation in an Thread?

Question

Since I don't know how much time a db operations will take, it will be ok to call them in threads?

At the moment I have a ThreadPool class which contains an ExecutorService object which run the jobs added. Every db-related methods is added as a job so the ThreadPool will run it when a new thread will be available.

Which is the best way to make db operations?

Thank you.

Answer 1

Since I don't know how much time a db operations will take, it will be ok to call them in threads?

Short asnwer: Yes. You should perform db operations in Threads. You should keep your application's logic separated from application's appearance.

Which is the best way to make db operations?

Generally using Threads is i think the best practise you can choose. And kind of used tool(PoolExecutor, FutureTask, AsyncTask, native Thread) depends what you are expecting and supposed time of operation(always if operations lasts more than 1-2 seconds, threads should be used).

AsyncTask is appropriate to use if you want to inform potential USER about about some progress in running task and it's worth to mention that is designated for tasks which will last ideally a few seconds.

For tasks that run for longer periods of time is appropriate to use mentioned API tools from java.util.concurrent package like ThreadPool, ThreadPoolExecutor, FutureTask etc.

I'm using AsyncTask or native Thread for db operations. Usually they last max. a few seconds. Always you can improve your performance related to insertion, deletion and updating with TRANSACTIONS. They can rapidly increase your for example insertion speed ¹ and also provide safer way to deal with your database. Always it's very good, efficient and useful practise.

---

¹ I made a few tests when i tested performance related to basic database operations and results were very interesting. I performed insertion of 100 000 records and difference was big. Whereas insertion with transaction took approx. 57,497 seconds, insertion without transaction took much more than 6 minutes. It's insane. Same with deletion and updating.

• letiagoalves: Did insertion without transaction took longer? Shouldn't be faster?
• Simon Dorociak: @letiagoalves yes, without transaction took much more longer how i mentioned. transaction rapidly increased speed. you can imagine insertion with transaction like batch inserts.
• letiagoalves: Do you have any explanation for that?
• Simon Dorociak: @letiagoalves so truly explanation is too hard but imagine this scenario: you are inserting to db normally so record is added - commit, second record added - commit, third record added - commit ---> so for every record is called commit operation and it takes some time. But if you will use transaction, autocommit is automatic assigned to false and you are performing operations like insert-insert-insert-insert-insert, ... n-insert ---> final commit for all records. How i mentioned it has behaviour as batch inserts.
• Simon Dorociak: @UngureanuLiviu so it depends how you implement it. Default is that if you wrap these 1000 queries into transaction and one fail so commit wont be called, db changes will be rolled back. But if you'll catch SQLException at 501. insert here you can commit transaction. But here, db integrity is broken. Imagine scenario: bank want to insert into 10 accounts cash. usually it have to be wrapped to transaction and transaction is successful if status of each account will be updated. if at least one fail, transaction must be rolled back. If this account would be yours, you would not be glad, isn't?

Answer 2

Perform async DB operations is totally fine.

I'm not sure which is the best way but AFAIK the standard one is to do it through Loaders (CursorLoader for accessing a ContentProvider or SQLiteCursorLoader for accessing SQLite databases).

You are free to use your own mechanism but be careful to prevent any potential memory leak.