Insert a list of objects into my room database at once, and the order of objects is changed

Question

I tried to insert a list of objects from my legacy litepal database into my room database, and when I retrieved them from my room database, I found the order of my objects is no longer the same as that of my old list. Below is my code:

// litepal is my legacy database
val litepalNoteList = LitePal.findAll(Note::class.java)

    if (litepalNoteList.isNotEmpty()) {

    litepalNoteList.forEach { note ->

        // before insertion, I want to trun my legacy note objects into Traininglog objects
        // note and Traninglog are of different types, but their content should be the same
        val noteContent = note.note_content
        val htmlContent = note.html_note_content
        val createdDate = note.created_date
        val isMarked = note.isLevelUp

        val legacyLog = TrainingLog(
            noteContent = noteContent,
            htmlLogContent = htmlContent,
            createdDate = createdDate,
            isMarked = isMarked)

        logViewModel.viewModelScope.launch(Dispatchers.IO) {
            trainingLogDao.insertNewTrainingLog(legacyLog)
    } // the end of forEach
}

The problem is that in my room database, the order of TraningLog objects differs randomly from that of my old list in the Litepal database.

Anyone konw why is this happening?

Answer 1

If the order matters, then you should extract data using the ORDER BY phrase. Otherwise you are leaving the order up to the query optimiser.

So say instead of @Query("SELECT * FROM trainingLog") then you could ORDER the result by using @Query("SELECT * FROM trainingLog ORDER BY createdDate ASC")

The efficiency of extracting the above would be improved by having an index on the createdDate column/field (in room @ColumnInfo(index = true)). However, it should be noted that there are overheads to having an index. Insertions and deletions and updates may incur additional processing to maintain the index. Additionally an index uses more space.

You may wish to have an insert function that can take a list rather than run multiple threaded inserts. Room will then (I believe) do all the inserts in a single transaction (1 disk write instead of many).

e.g.

instead of or as well as

@Insert
fun insert(trainingLog: TrainingLog): Long

you could have

@Insert
fun insert(trainingLogList: List<TrainingLog>): LongArray

Then all you need to do is build the List in your loop and then after the loop invoke the single insert.