What is faster on Android, SQLiteDatabase.Insert() or SQLiteDatabase.execSQL()?

Question

Hello I would like to know what method is faster on Android.

I have a loop that process thousands of rows and I think the performance is being affected with the SQLiteDatabase.insert() method.

In the insert method I put a parameter with contentvalues and I think in the background the method must to have a process to check every contentvalue and build the query.

Instead in the execSQL method I put the whole query as a parameter and I think the method don't have to do that much to execute it.

I don't know if i am right, I think the execSQL is better than insert for a considerable quantity of data but I am not sure...

Answer 1

Well i tried that and there is a little difference beteween them... The execSQL is faster but it's difficult to notice it.

However, my real problem was that I wasn't using transactions. Due to sqlite doesn't have a server to control the access to the data, sqlite has to close and reopen the phisycal file for every insert you do. so if you have a thousand inserts in a loop and there's no transaction for it, it will going to do a thousand transactions. In my test, a thousand inserts lasted 11 seconds without using transaction, and with transaction it lasted just 1 second.

Answer 2

Most probably you won't see any difference. Most of the time is spent to actually write the data into the database and the query generation is rather simple step. I recommend you to time the inserts using both ways if you want to see the difference yourself.