Do efficient SQLite Inserts with Android

Question

I've got an activity that handles the printers of my University. The Printers can be downloaded over the internet and are then stored in a SQLite database. The problem is, that there are about 500 database entries that have to be made to store them, which is with my code very time intensive (approx. 30s on a Google Nexus S). My code to do so is this:

printerDB = new PrinterListOpenHelper(this);
SQLiteDatabase db = printerDB.getWritableDatabase();
db.execSQL("INSERT INTO destination (id, destination) VALUES(1,'BSAC240');");
db.execSQL("INSERT INTO destination (id, destination) VALUES(2,'BSAD152');");
...

This is followed by approx. 500 similar rows. I also tried to do it with a single

db.rawQuerry("INSERT INTO destination (id, destination) VALUES(1,'BSAC240');
INSERT INTO destination (id, destination) VALUES(2,'BSAD152');.......");

but then only the first INSERT statement is actually executed.

Does anybody know a trick for making this efficient? Or are the Android Databases actually that slow?

Thanks a lot for your help! Simon

vickirk · Accepted Answer · 2011-03-15 12:07:10Z

17

Does it work any better if you put them into a transaction?

BEGIN TRANSACTION
  INSERT INTO destination (id, destination) VALUES(1,'BSAC240');
  INSERT INTO destination (id, destination) VALUES(2,'BSAD152');
END TRANSACTION

Or even (updated following comments)

db.beginTransaction()
try {
    db.execSQL("INSERT INTO destination (id, destination) VALUES(1,'BSAC240');");
    db.execSQL("INSERT INTO destination (id, destination) VALUES(2,'BSAD152');");
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

edited Mar 15, 2011 at 12:07

answered Mar 15, 2011 at 11:29

vickirk

4,0672 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vickirk Over a year ago

Well spotted, and obviously wrap it with a try/catch as well

Simon Rösch Over a year ago

Thx! That was exactly what I was looking for, helped me a lot! Do you know why we technically have to do it this way?

vickirk Over a year ago

Wrapping it in a transaction means they are all done together exclusively. Without that each one is done in its own.

Collectives™ on Stack Overflow

Do efficient SQLite Inserts with Android

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related