CODEWITHSUNDEEP
androidsqlitequery-optimization
Remon Shehatta
Remon Shehatta

Reputation: 1470

deleting records in sqllite is faster one by one or as a list

which is faster:  

  1. to delete records each one at a time

e.g. listOfObjects.forEach{ repository.delete(it.id) }

and here is the query

@Query("DELETE FROM myTable WHERE object_id=:id")
fun delete(objectId: Long)

2.    to delete the whole list in a single query

e.g. repository.delete(listOfObjectIds)

and here is the query

@Query("DELETE FROM myTable WHERE object_id=:id in(:objectIds)")
fun delete(ObjectIds: List<Long>)

?

Upvotes: 0

Views: 180

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

By far, the second list version will outperform the first version, which issues N delete statements for a list of size N. The main reason is that the second version requires only one round trip to and from your SQLite database, while the first version requires N trips.

In addition, the second version has the added benefit that your entire logical delete operation can execute within a single transaction. The first version would, by default, execute across multiple transactions. This could lead to potential problems should, for example, an exception occur halfway through the batch of single delete operations.

Upvotes: 2

Related Questions