How do I achieve best performance using SQLite in Android?

Question

I've noticed that there are multiple ways to do SQLite operations (query, insert, update, delete), and some can be faster than the rest. Many websites provide different tips, and some conflict with the others.

1. It seems that using a transaction for bulk insert is somehow faster than doing so in a loop.

   How come? What is the best way to achieve best performance when using SQLite? How does sqlite work on Android?

2. Confusing with using InserHelper vs ContentValues, as shown here.

   How does the InserHelper work and will it always be faster than ContentValues? Will wrapping it with a transaction speed it up even further?

3. Confusion on other SQL frameworks. I remember that adding indices would boost the performance of queries related to the indices. Is this true for Android too? Is this a good practice?

4. I've heard that in some cases, it's best to not use the UI thread for DB operations. In which cases is this recommended? As in, how slow could DB operation become? If the DB becomes 70MB, does it mean that it will be much slower and UI thread should never handle it?

Answer 1

how come?

Transactions involve disk I/O. It is much less expensive to do one larger chunk of disk I/O than lots of little chunks of disk I/O.

how does the InserHelper work and will it always be faster than contentValues?

Quoting the documentation: "This class allows users to do multiple inserts into a table but compile the SQL insert statement only once, which may increase performance."

will wrapping it with a transaction speed it even further ?

Presumably yes.

is it true for android too ?

Indexes can increase query speed, if you create the proper indexes for whatever queries you are performing. Indexes always decrease insert/update/delete speed, as now those indexes need to be updated. This has nothing in particular to do with Android.

is is a good thing to use ?

That is impossible to answer in the abstract. This has nothing in particular to do with Android.

it's best to not use the UI thread for DB operations

Correct.

in which cases will it be recommended ?

It is never recommended to perform any disk I/O on the main application thread.

how slow could DB operation become ?

Centuries. Probably by then, something will fail in the device, or it will run out of battery power.

if the DB becomes 70MB , does it mean that it will be much slower

It depends upon what you do with it.

and UI thread should never handle it ?

Correct.