Hi everyone, I'm having trouble with the performan...

# android

[PhongBM]

Hi everyone,
I'm having trouble with the performance of Room Database.
I need to execute concurrent queries to the same table, like the code below. I noticed the times of these queries increasing gradually, as if they were dependent on each other.

s = System.currentTimeMillis()
invoiceIds.map {
    async {
        val t = System.currentTimeMillis()
        val invoice = invoiceDao.findById(it.webId)
        Log.d(TAG, "${System.currentTimeMillis() - t} ms")
        invoice
    }
}.awaitAll()
Log.d(TAG, "Total ${System.currentTimeMillis() - s} ms")

What is the cause and is there a way to fix it?
Thanks in advance!

[Brian Cooley]

Hi! Performance will likely be better if you execute a single query. To your invoiceDao interface, you could add a method that takes a list of whatever type webId is. For example, the following might do the trick:

@Query("SELECT * FROM invoices WHERE webId IN (:webIds)")
fun findByIds(webIds: List<Int>): List<Invoice>

Then you could map the invoiceIds to a list of webIds and make a single call to invoiceDao.findByIds(webIds) . As a bonus you'd get to drop the awaitAll() call since it is just one query.

See https://developer.android.com/training/data-storage/room/accessing-data#collection-parameters for a reference.

Here's a Stack Overflow question that discusses the relative performance, but YMMV and you'll want to test for yourself: https://stackoverflow.com/questions/5803472/sql-where-id-in-id1-id2-idn

Finally, just wanted to mention that Kotlin has a convenient measureTimeMillis that can ease the pain of timing method calls. No more need to repeatedly call System.currentTimeMillis()! See https://kotlinlang.org/api/latest/jvm/stdlib/kotlin.system/measure-time-millis.html

Good luck and happy coding!

[PhongBM]

Thanks for your detailed help.

However that's just my example of the query statement. In my project, the async statement does a complex job of querying the database. When I did them all at the same time, I noticed that the execution time of each of them increased.
Does Room support concurrent queries with better performance?

Example: If I have 10 queries
1. If executing queries sequentially (use for, await)
query 1: 2ms
query 2: 3ms
query 3: 2ms
...
query 10: 3ms
2. If executing concurrent queries (map, async, awaitAll)
query 1: 2ms
query 2: 3ms
query 4: 4ms
query 5: 6ms
...
query 10: 15ms

[Brian Cooley]

Does Room support concurrent queries with better performance?

Room is just an abstraction layer over SQLite, so you are bound to the concurrency characteristics of SQLite.

I don't know that much about concurrency in a SQLite database. My understanding is that reads are concurrent but writes are sequential. And reads lock out writes.

Write-ahead logging and transactions are usually what is recommended to improve performance. I think WAL is on by default in Room. I don't have much guidance to offer regarding transactions.

From a Kotlin coroutine perspective, it's always good to keep in mind that 1 coroutine != 1 thread. Each dispatcher is backed by a different thread pool. For database queries, I use <http://Dispatchers.IO|Dispatchers.IO> which is designed for blocking operations and uses a larger thread pool, I think it is 64 threads. Dispatchers.Default thread pool is the number of cores on the device IIRC, and Dispatchers.Main will be just 1 thread, the main thread.