Sunday, July 25, 2010

Notes on Android SQLite bulk insert

There's no real bulk insert in SQLite I'm aware of to date. I've tried various ways to insert data fast into Android's SQLite DB, and here are my notes:
1. Use transactions. (e.g. db.beginTransaction, etc..).
2. Use compiled statement: db.compileStatement(insertStatementString), it's much faster than db.insert().

So, in short:

db.beginTransaction();
try {
string sql = "insert into products (id) values (?)";
SQLiteStatement insert = db.compileStatement(sql);

for (.....) {
insert.bindLong(1, 12345);
insert.executeInsert();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}

5 comments:

Jordi said...

Thank you very much! This solves my problem, thanks again!

regards!

karthik said...

hi,
this code is fine but how to create the begintransaction method in database class. can u explain.
thanks in advance

DaveT said...

Wow.. nice post! .. I was inserting about 300-400 rows in an init process utilizing the "Content Values" and db.insert method. It was taking approximately 20-30 seconds for the Http Connection, truncate and inserts. rewrote it using bind parameters and the transaction.. Got the process down to 3 seconds..

Side bar point: The old insert process took 30 seconds on a new Galaxy Nexus but only 8 seconds on an HTC Thunderbolt.. weird..?..

Thanks for Sharing

- Dave

Bridging Loans said...

I can't thank you enough for sharing this - It knocked over a minute off the time taken to populate a large arrayList of data.

Fantastic!

mohd said...

nice post
Thank You