Android: Why is Room so slow?

Question

I am working on a simple database procedure in Kotlin using Room, and I can't explain why the process is so slow, mostly on the Android Studio emulator.

The table I am working on is this:

@Entity(tableName = "folders_items_table", indices = arrayOf(Index(value = ["folder_name"]), Index(value = ["item_id"])))
data class FoldersItems(
        @PrimaryKey(autoGenerate = true)
        var uid: Long = 0L,

        @ColumnInfo(name = "folder_name")
        var folder_name: String = "",

        @ColumnInfo(name = "item_id")
        var item_id: String = ""

)

And what I am just trying to do is this: checking if a combination folder/item is already present, insert a new record. If not, ignore it. on the emulator, it takes up to 7-8 seconds to insert 100 records. On a real device, it is much faster, but still, it takes around 3-4 seconds which is not acceptable for just 100 records. It looks like the "insert" query is particularly slow.

Here is the procedure that makes what I have just described (inside a coroutine):

val vsmFoldersItems = FoldersItems()

items.forEach{

    val itmCk = database.checkFolderItem(item.folder_name, it)


    if (itmCk == 0L) {


        val newFolderItemHere = vsmFoldersItems.copy(
                                            folder_name = item.folder_name,
                                            item_id = it
        )
    
        database.insertFolderItems(newFolderItemHere)

    }
    
}

the variable "items" is an array of Strings.

Here is the DAO definitions of the above-called functions:

@Query("SELECT uid FROM folders_items_table WHERE folder_name = :folder AND item_id = :item")
    fun checkFolderItem(folder: String, item: String): Long

@Insert
    suspend fun insertFolderItems(item: FoldersItems)

I think you should just [create an unique index](https://stackoverflow.com/a/48962768/9241978) out of your value columns and just run one batch insert instead of looping and running a check query followed by insert query for every single item. — Pawel, May 17 '21 at 21:36
Thank you @Pawel, I tried that but the fact is that since this procedure is called asynchronously, as soon as the insert query tries to store a value already there, it makes an exception and the loop is broken. So, I have to stick with my simple query check... thanks anyway! — Fabrizio Ferrari, May 18 '21 at 21:46
What exception does it make? If I wasn't clear by "batch insert" I meant to modify (or add new) DAO method `insertFolderItems` to consume entire list with `OnConflictStrategy.IGNORE` and you wouldn't have any loop whatsoever. — Pawel, May 18 '21 at 22:33
Good point @Pawel, I didn't think about that. I'll try that tomorrow and report. Thank you again. — Fabrizio Ferrari, May 18 '21 at 23:52

score 1 · Accepted Answer · answered May 18 '21 at 05:17

Placing the loop inside a single transaction should significantly reduce the time taken.

The reason is that each transaction (by default each SQL statement that makes a change to the database) will result in a disk write. So that's 100 disk writes for your loop.

If you begin a transaction before the loop and then set the transaction successful when the loop is completed and then end the transaction a single disk write is required.

What I am unsure of is exactly how to do this when using a suspended function (not that familiar with Kotlin).

As such I'd suggest either dropping the suspend or having another Dao for use within loops.

Then have something like :-

val vsmFoldersItems = FoldersItems()
your_RoomDatabase.beginTransaction()
items.forEach{

    val itmCk = database.checkFolderItem(item.folder_name, it)


    if (itmCk == 0L) {


        val newFolderItemHere = vsmFoldersItems.copy(
                                            folder_name = item.folder_name,
                                            item_id = it
        )
    
        database.insertFolderItems(newFolderItemHere)

    }
    
}
your_RoomDatabase.setTransactionSuccessful() //<<<<<<< IF NOT set then ALL updates will be rolled back
your_RoomDatabase.endTransaction()

You may wish to refer to:-

https://developer.android.com/reference/androidx/room/RoomDatabase

You may wish to especially refer to runInTransaction

Just have that code in an abstract method inside the DAO class (convert it from an interface to an abstract class first) and annotate the method with @Transaction. The entire method should then be executed in a single transaction. — cd1, May 18 '21 at 14:07
Fantastic! Thank you guys! I endedup using the runInTransaction() which is the most current one, and now the loop takes less than a second. Thank you again very much! — Fabrizio Ferrari, May 18 '21 at 21:47
Hey Mike, I am just using select query in 2000 rows but it takes litterly 6 to 8 sec to fetch data? can you help me with that ? — Gulab Sagevadiya, Sep 29 '21 at 03:56
for those coming here and wanting the coroutines solution, use `appDatabase.withTransaction { ... }` — Tyler Turnbull, Dec 31 '22 at 02:47

Android: Why is Room so slow?

1 Answers1

Linked