Android Room. Which method of insertion will be faster?

Question

I am using Room as an abstraction layer over SQLite. After reading this page I found out that we can insert multiple objects at the same time. Currently I use a For loop to insert objects, i.e one object in each For loop iteration. The two ways of inserting that I know of currently are:

1. Using a For loop and inserting each object one at a time

   @Insert(onConflict = OnConflictStrategy.REPLACE) public void addActivity(Person person);

2. Inserting an array or a list of objects.

   @Insert(onConflict = OnConflictStrategy.REPLACE) public void insertUsers(Person ... people);

When I was coding the insertion of objects, I did not know of the second way of insertion. Now I want to know if there is any noticeable difference in speeds between the two ways so that I can change my code to increase performance of my app.

Answer 1

As requested by OP in a comment of their question, here's (for the sake of clarity, as an answer) what I did to check the performance:

Before, inserting objects one by one:

@Dao
abstract class MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract fun insert(items: MyObject): Long
    // ...
}

Syncing code:

val response = backend.downloadItems() // download from server
val items = response.getData() // this is a List<MyObject>
if (items != null) {
    for (i in items) {
        myDao.persist(s)
    }
}

This took a minute on a Huawei P10+.

I changed this to:

@Dao
abstract class MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract fun insert(items: Iterable<MyObject>)
    // ...
}

Syncing code:

val response = backend.downloadItems() // download from server
val items = response.getData() // this is a List<MyObject>
response.getData()?.let { myDao.insert(it) }

This took less than a second.

The point here is to use specifically the Iterable<> version of the DAO @Insert method, which as said by @iDemigod, uses the Iterable<> version of the EntityInsertionAdapter.

The body of said function is in @iDemigod's answer, and it uses a single prepared statement for all the insertions.

Parsing the SQL into a statement is expensive, and using a statement creates a transaction for the whole insert batch, which can help solve other issues (I had an observable LiveData<> on the database, which was notified 12k times during the insert... performance was awful).

Answer 2

Under the hood Room generated classes are using EntityInsertionAdapter for this particular situation. And there is two methods, we need to check:

1. This one is used for inserting a single entity

    public final long insertAndReturnId(T entity) {
       final SupportSQLiteStatement stmt = acquire();
       try {
           bind(stmt, entity);
           return stmt.executeInsert();
       } finally {
           release(stmt);
       }
   }
   

2. While this one is used to insert an array of entities

   public final void insert(Iterable<T> entities) {
       final SupportSQLiteStatement stmt = acquire();
       try {
           for (T entity : entities) {
               bind(stmt, entity);
               stmt.executeInsert();
           }
       } finally {
           release(stmt);
       }
   }
   

AS you can see the internals are pretty much the same as yours - stmt.executeInsert(); is called once or in the loop. The only performance change using the insertUsers method I can think of is the change notification, which will happen only once, when all the users will be inserted. But if you're already doing you insertion in the loop wrapped with @Transaction then there would be no change.