I have around 7000 rows which want to insert into device's(Android galaxy tab2) sqlite database. Currently I am parsing the JSON file and inserting the row into the database using "for" loop. But its taking more than 120 seconds to parse and inset data into sqlite database. Is there any other faster way to do this?
Thanks...
You should use statements
database.beginTransaction();
SQLiteStatement stmt = database.compileStatement(sql);
for (int i = 0; i < NUMBER_OF_ROWS; i++) {
//generate some values
stmt.bindString(1, randomName);
stmt.bindString(2, randomDescription);
stmt.bindDouble(3, randomPrice);
stmt.bindLong(4, randomNumber);
long entryID = stmt.executeInsert();
stmt.clearBindings();
}
database.setTransactionSuccessful();
database.endTransaction();
Reference
Here is one more link where a comparison between these two insertion methods is given. (for 100 records, normal insertion took 1657ms, while statements with endTransaction() took 92ms)
I have ran into the exact same situation. First I was using DatabaseUtil.insertHelper, and it was taking me +30 seconds for 600 entries.
I did a bit more digging and found out that every row i'm inserting is being committed, which is extremely expensive.
To solve this, I switched to ORMLite, with the insertAll method:
/**
* Inserts multiple objects into the database quickly by committing them all at once
*
* @param objects The objects to insert
*/
public void insertAll(final T[] objects) {
try {
dao.callBatchTasks(new Callable<Void>() {
@Override
public Void call() throws Exception {
for (T object : objects) {
dao.create(object);
}
return null;
}
});
} catch (Exception e) {
AdbLogger.error("Error inserting new " + className + "s in the database");
}
}
The idea here is that they do the insert of the array of object, then at the end only do one big commit. After this switch the insert became almost instantanious.
