What is fastest way to insert rows in sqlite database

Question

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...

can you insert all data with one query? or by batch, like 500 per batch? — dongshengcn, Apr 18 '13 at 17:52
Another idea would be pre-insert to a sqlite data, and host the database file somewhere, and download the file and open it directly. That is rather dramatic changes. — dongshengcn, Apr 18 '13 at 17:53
@dongshengcn : good idea! but i think, i have to create a .sqlite file first. Can i generate a .sqlite file from .sql? — RHL, Apr 18 '13 at 18:02
This has a lot great information here: [Improve INSERT-per-second performance of SQLite](https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — Chronial, Oct 08 '21 at 21:14

score 1 · Answer 1 · answered Apr 18 '13 at 17:58

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.

@wangyyif2 : Thanks a lot, i'll try this. hope it'll work :-) — RHL, Apr 18 '13 at 18:07

score 1 · Accepted Answer · edited May 23 '17 at 12:30

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)

What is fastest way to insert rows in sqlite database

2 Answers2