android and sqlite performance

Question

I'm having a serious performance issue with sqlite, and Android 2.3.7.

I have a small amount of data to store: 14 rows with eight columns each. Data is read from a web page; when data is parsed I'll just delete the whole content of the database and then bulkInsert the new data in one go.

Some (edited) LogCat output:

03-15 14:39:51.909: Got content downloaded.
03-15 14:39:52.299: Deleting existing info from db.
03-15 14:39:52.719: Bulk inserting new data into db.
03-15 14:40:00.709: Stored data in the database.

I am using a ContentProvider to access the database.

Parsing the page takes 0.4 seconds.

The delete takes 0.4 seconds.

The bulkInsert takes 8 seconds.

A subsequent query and read of all the data takes again about 0.4 seconds.

Relevant code snippet:

    Log.v(TAG, "Deleting existing info from db.");
    delete(API_TABLE, null, null);
    Log.v(TAG, "Bulk inserting new data into db.");
    bulkinsert(API_TABLE, allValues);
    Log.v(TAG, "Stored data in the database.");

Field and type data:

public final static String[] API_KEYS = {KEY_STATION, KEY_API, 
    KEY_POLLUTANT, KEY_SO2, KEY_NOX, KEY_CO, KEY_PHOTO, KEY_TSPRSP, 
    KEY_RSP};
public final static String[] API_TYPES = {"TEXT PRIMARY KEY", "TEXT", 
    "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT"};

The database construction is done like this:

        StringBuilder dbCommand = new StringBuilder();
        dbCommand.append("CREATE TABLE " + API_TABLE + " (");
        dbCommand.append("'" + API_KEYS[0] + "' " + API_TYPES[0]);
        for (Integer i = 1; i < API_KEYS.length; i++)
            dbCommand.append(", '" + API_KEYS[i] + "' " + API_TYPES[i]);
        dbCommand.append(");");
        Log.v(TAG, "Creating API database now.");
        db.execSQL(dbCommand.toString());

The values to insert are stored in a ContentValues[] object, and then inserted using

context.getContentResolver().bulkInsert(API_URI, values);

Any ideas on how to improve on this? Especially the insert simply takes way too long.

Answer 1

Based on reply by StinePike, I changed my provider class to include:

@Override
public int bulkInsert(Uri uri, ContentValues[] values) {

    String table = getTable(uri);
    int count = 0;
    for (ContentValues value : values) 
        count += db.insert(table, "", value);

    return count;

}

This improves the insert time a little bit, but 4-5 seconds is still a bit long for just 14 values.

Then searching a bit more in this direction I found a different solution here which really speeds it up, I'm now down to less than 1 second. And that's acceptable, I don't think can improve it much more. This code is part of my ContentProvider extension class.

    @Override
public int bulkInsert(Uri uri, ContentValues[] values) {
    try {
        db.beginTransaction();
        String table = getTable(uri);
        for (ContentValues value : values)
            db.insert(table, null, value);
        db.setTransactionSuccessful();
    } catch (SQLException e) {
    } finally {
        db.endTransaction();
    }
    return values.length;   
}

Answer 2

use SQLiteDatabase class for this operations.

I used following method to insert and it was relatively faster

public void createContatctDirectory(ArrayList<String> data) {
    SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME,
            Context.MODE_PRIVATE, null);

    InsertHelper ih = new InsertHelper(db, TABLE_NAME);
    Iterator<String> it = data.iterator();
    final int numberColumn = ih.getColumnIndex(KEY_NUMBER);

    db.beginTransaction();
    int count = 0;
    try {
        while (it.hasNext()) {
            ++count;
            String ph;
            ph = it.next();
            ih.prepareForInsert();
            ih.bind(numberColumn, ph);

            ih.execute();
        }
    } finally {
        ih.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
}