Code writing to SQLite is slow

Question

The code below takes data from an ArrayList and writes it to the SQLite database on the device.It runs fairly slow, with an ArrayList size of about 800, it takes about 1.5 minutes.Do you see anything that could make it run faster?

Iterator<PermitData> iterator = permitDataArrayList.iterator();
while (iterator.hasNext()) {
    PermitData permitData = (PermitData) iterator.next();

    HashMap<String, String> queryValues = new HashMap<String, String>();

    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_ID, Integer.toString(permitData.Id));
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_TYPE, permitData.Type);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_NAME, permitData.Name);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD1, permitData.Field1);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD2, permitData.Field2);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD3, permitData.Field3);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD4, permitData.Field4);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD5, permitData.Field5);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD6, permitData.Field6);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD7, permitData.Field7);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD8, permitData.Field8);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD9, permitData.Field9);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD10, permitData.Field10);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD11, permitData.Field11);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD12, permitData.Field12);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD13, permitData.Field13);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD14, permitData.Field14);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD15, permitData.Field15);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_TO_DATE, permitData.ToDate);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FROM_DATE, permitData.FromDate);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD16, permitData.Field16);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD17, permitData.Field17);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_ADDRESS, permitData.Address);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_PHONE, permitData.Phone);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD18, permitData.Field18);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_EMAIL, permitData.Email);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD19, permitData.Field19);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_XCOORD, permitData.XCoord);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_YCOORD, permitData.YCoord);
    queryValues.put(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_FIELD20, permitData.Field20);

    try {
        sqLiteManager.insertOrUpdatePermit(PermitDataContract.PermitDataEntry.TABLE_NAME, queryValues);
    }
    catch (Exception e) {
        Log.d("StoreData", " Id: " + queryValues.get(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_ID) + " Name: " + queryValues.get(PermitDataContract.PermitDataEntry.COLUMN_NAME_PERMIT_NAME));
    }

} // end while

SQLiteManager.java

public synchronized void insertOrUpdatePermit(String tableName, HashMap<String, String> queryValues) {

    ContentValues contentValues = new ContentValues();

    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_ID, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_ID));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_TYPE, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_TYPE));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_NAME, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_NAME));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD1, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD1));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD2, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD2));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD3, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD3));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD4, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD4));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD5, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD5));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD6, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD6));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD7, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD7));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD8, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD8));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD9, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD9));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD10, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD10));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD11, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD11));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD12, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD12));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD13, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD13));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD14, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD14));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD15, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD15));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FROM_DATE, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FROM_DATE));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_TO_DATE, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_TO_DATE));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD16, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD16));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD17, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD17));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_ADDRESS, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_ADDRESS));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_PHONE, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_PHONE));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD18, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD18));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_EMAIL, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_EMAIL));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD19, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD19));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_XCOORD, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_XCOORD));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_YCOORD, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_YCOORD));
    contentValues.put(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD20, queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_FIELD20));

    String permitId = queryValues.get(PermitDataEntry.COLUMN_NAME_PERMIT_ID);
    String columns[] = {PermitDataEntry.COLUMN_NAME_PERMIT_ID};
    Cursor cursor = null;
    try {
        cursor = mDatabase.query(tableName, columns, PermitDataEntry.COLUMN_NAME_PERMIT_ID + "=?", new String[]{permitId}, null, null, null);
    }
    catch (Exception e) {
        Log.e("insertOrUpdatePermit", "exception:cursor: " + tableName + " columns: " + columns);
        e.printStackTrace();
    }

    // if count is 0, then permitId does not exist, so insert
    // if count is 1, then permitId does exist, so update
    int count = cursor.getCount();
    if (count == 0) { // if new row
        mDatabase.insert(tableName, null, contentValues);
    }
    else { // primary key already exists
        mDatabase.update(tableName, contentValues, null, null);
    }

    cursor.close();
}

Answer 1

By default, the boundaries of a SQLite statement (e.g., insert(),update(),execSQL()`) is that individual statement. This means that your code is doing 800 transactions. Each transaction involves disk I/O, to update the database and transaction log. Doing lots of little transactions gets slow.

For bulk data operations, it is better to wrap your own transaction around the work. Partly, that will be for speed. Partly, that way the whole bulk data load will succeed or fail as a whole, so if it fails (e.g., foreign key constraint violation), you do not wind up with a mix of succeeded and failed operations.

The pseudo-Java for this is:

db.beginTransaction();

try {
  // do real SQL calls here
  db.setTransactionSuccesful();
}
finally {
  db.endTransaction();
}

(where db is a SQLiteDatabase, and catch blocks are optional)