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.

I'm not quite sure how you implemented your "bulk inserts". Are you doing this in a single transaction, and have you tried the pointers from outofwhatbox.com/blog/2010/12/… ? — Paul-Jan
– Paul-Jan, Commented Mar 15, 2013 at 7:40
Bulk insert is done with a call to context.getContentResolver().bulkInsert(API_URI, values); with values a list of ContentValues, each of which contains the data for a single row. Edited question to reflect this. — Wouter
– Wouter, Commented Mar 15, 2013 at 8:58

Wouter · Accepted Answer · 2013-03-18 16:03:56Z

2

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;   
}

edited Mar 18, 2013 at 16:03

answered Mar 15, 2013 at 9:23

Wouter

2,6534 gold badges37 silver badges45 bronze badges

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

1 Comment

Wolfram Rittmeyer Over a year ago

Though this might not help that much, you should also use getTable(uri) only once. No need to parse the tablename within each iteration anew. The second parameter nullColumnHack should be null. And finally: Your count value will be wrong. The method insert() returns the id of the row.

stinepike · Accepted Answer · 2013-03-15 09:30:57Z

0

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();
    }
}

edited Mar 15, 2013 at 9:30

answered Mar 15, 2013 at 7:46

stinepike

54.9k14 gold badges95 silver badges116 bronze badges

3 Comments

stinepike Over a year ago

@CL does context.getContentResolver().bulkInsert works same as SQLiteDatabase? I don't know

CL. Over a year ago

It ends up at the same database object. This would make a difference only if you'd want to call other functions on the SQLiteDatabase object (but you didn't mention that).

Wouter Over a year ago

@CL it was done indirectly, via the ContentProvider. The bulkInsert method is also part of the ContentProvider class. No direct call to SQLiteDatabase in that case.

Collectives™ on Stack Overflow

android and sqlite performance

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related