Inserting rows in Sqlite slow

Question

I am attempting to insert rows into a SQLite DB. It takes 7 seconds to insert 130 rows, this seems slow to me.

Table definition is :

    sd.execSQL("CREATE TABLE " + V2VocabsTable.TABLE_NAME + " (" 
            + V2VocabsTable.ID   + " VARCHAR(10) PRIMARY KEY ," 
            + V2VocabsTable.NAME    + " VARCHAR(255), " 
            + V2VocabsTable.LOADED  + " VARCHAR(1) " 
            + ");"
            );

To insert a row I use the following code :

public void addTableStatus(String tableId, String name) {

    Log.d("DB","Before Insert");

    ContentValues cv = new ContentValues();
    cv.put(V2VocabsTable.ID, "Table " + tableId);
    cv.put(V2VocabsTable.NAME, name);
    cv.put(V2VocabsTable.LOADED, "NO");
    SQLiteDatabase sd = getWritableDatabase();
    long result = sd.insert(V2VocabsTable.TABLE_NAME,   null, cv);

    Log.d("DB","After Insert :: " + result);
    }

Times in the log file are :

 12-17 23:00:11.445: D/SQLDB(25384): Init
 12-17 23:00:11.455: D/DB(25384): Before Insert
 12-17 23:00:11.505: D/SQLDB(25384): Open
 12-17 23:00:11.895: D/DB(25384): After Insert :: 1
 12-17 23:00:11.895: D/DB(25384): 0001 :: AdministrativeSex-v1.0.xml
 12-17 23:00:11.895: D/DB(25384): Before Insert
 12-17 23:00:11.965: D/DB(25384): After Insert :: 2
 12-17 23:00:11.965: D/DB(25384): 0002 :: MaritalStatus-v1.0.xml
 12-17 23:00:11.965: D/DB(25384): Before Insert
 12-17 23:00:12.005: D/DB(25384): After Insert :: 3
 12-17 23:00:12.005: D/DB(25384): 0004 :: PatientClass-v1.0.xml
 12-17 23:00:12.005: D/DB(25384): Before Insert
 12-17 23:00:12.145: D/DB(25384): After Insert :: 4

 12-17 23:00:18.815: D/DB(25384): Before Insert
 12-17 23:00:18.865: D/DB(25384): After Insert :: 131
 12-17 23:00:18.865: D/DB(25384): ZU057 :: LocationHiding-v1.0.xml
 12-17 23:00:18.865: D/DB(25384): Before Insert
 12-17 23:00:18.925: D/DB(25384): After Insert :: 132

This seems excessively slow to me, what could be slowing it down?

I'm not sure why you are calling `getWritableDatabase()` for each insertion, but the answers here may interest you: [Bulk Insertion on Android device](http://stackoverflow.com/q/3860008/1267661). — Sam, Dec 17 '12 at 23:19
Use Traceview to determine specifically where performance issues lie. — CommonsWare, Dec 17 '12 at 23:30

wtsang02 · Accepted Answer · 2012-12-18T14:50:18.893

public DataBase openWithTrans() throws SQLException {
    ourHelper = new DataBaseHelper(ourContext, DATABASE_NAME,
            DATABASE_LOCATION);
    db = ourHelper.getWritableDatabase();
    db.beginTransaction();
    return this;
}

public void closeWithTrans() {
    try {

        db.setTransactionSuccessful();


    } catch (Exception e) {
        e.printStackTrace();
    }finally{

        db.endTransaction();
        Debug.debugMsg("end trans successful");
        db.close();
        Debug.debugMsg("close db successful");
        if (ourHelper != null) {
            ourHelper.close();
        }
    }

}

The above code will will enable you to do transaction. In transcation mode you should be able to increase your speed up by a lot. And as comment pointed out, you start transaction then insert your x number of rows then close it.

The important thing is to use *one* transaction for all 130 inserts. — CL., Dec 18 '12 at 11:27
This was exactly what was required. I placed all of the inserts within one transactions and the 130 inserts all happened within 0.3 seconds. — BENBUN Coder, Dec 19 '12 at 10:02

score 0 · Answer 2 · answered Dec 17 '12 at 23:19

DB operations can be very slow, because of IPC involved, and the SD card access time, but I haven't measured it. But instead of doing hundreds of inserts, you can do a bulkInsert or applyBatch. Are you planning to investigate why it is slow or looking for a faster approach?

score 0 · Answer 3 · answered Dec 17 '12 at 23:33

0

as an alternative, you can keep build-ready database file in assest and hard copy/write under /data/data/XX/database.

It's good for one shot Installs.

answered Dec 17 '12 at 23:33

Mert

6,432
6
32
68

Inserting rows in Sqlite slow

3 Answers3