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. — Sam
– Sam, Commented Dec 17, 2012 at 23:19
Use Traceview to determine specifically where performance issues lie. — CommonsWare
– CommonsWare, Commented Dec 17, 2012 at 23:30

wtsang02 · Accepted Answer · 2012-12-18 14:50:18Z

1

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.

edited Dec 18, 2012 at 14:50

answered Dec 18, 2012 at 0:37

wtsang02

18.9k10 gold badges51 silver badges68 bronze badges

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

2 Comments

CL. Over a year ago

The important thing is to use one transaction for all 130 inserts.

BENBUN Coder Over a year ago

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.

Durairaj Packirisamy · Accepted Answer · 2012-12-17 23:19:17Z

0

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?

answered Dec 17, 2012 at 23:19

Durairaj Packirisamy

4,7351 gold badge25 silver badges27 bronze badges

Comments

Mert · Accepted Answer · 2012-12-17 23:33:40Z

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, 2012 at 23:33

Mert

6,6127 gold badges34 silver badges74 bronze badges

Collectives™ on Stack Overflow

Inserting rows in Sqlite slow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related