"Optimizing" access to cursors in Android: Position vs Column names

Question

From the performance point of view: Is it fine if in every access to my cursors I use something like :

public static final String COLUMN_NAME = "my_column_name";
cursor.getString(cursor.getColumnIndex(COLUMN_NAME));

Or I should see a measurable improvement in performance if I use this instead:

public static final int COLUMN_POSITION = #column_position;
cursor.getString(COLUMN_POSITION);

I prefer the first approach since the rest of the code is not depending on the position of the columns in the query, but only on the name of the column.

Is worth sacrificing this for the "performance improvement" of accessing the cursor using constant positions instead?

Answer 1

To answer your question (and mine by the way), i've made a few tests.

This test was basically to check how much time the query took for those two cases:

1. Using the cursor.getString(cursor.getColumnIndex(COLUMN_NAME)) method
2. Getting the column id first and then calling directly the cursor.getString(COLUMN_POSITION) method

To make the performance test significative i've inserted 5000 lines in a database and then i've made a query threw my ContentProvider on those elements.

Results:

 ___________________________________________________________________________
| Column count| Time (ms) getColumnIndex | Time (ms) columnId | improvement |
|_____________|__________________________|____________________|_____________|
| 500         | 34564                    | 30401              | 13%         |
| 200         |  9987                    |  8502              | 17%         |
| 100         |  4713                    |  4004              | 17%         |
| 50          |  2400                    |  1971              | 21%         |
| 20          |  1088                    |   915              | 19%         |
|___________________________________________________________________________|

So, getting the column id first and the call directly the getString()method will take around 20% less time.

---

Test method details :

Platform : Nexus 7 (2012) on Android 4.3

Database creation :

public static int TESTSPEEDCOLUMNCOUNT = 200;
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE " + Tables.TESTSPEED + " (");
sb.append(BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, ");
for (int i = 0; i < (TESTSPEEDCOLUMNCOUNT - 1); ++i) {
    sb.append("C" + i + " TEXT, ");
}
sb.append("C" + (TESTSPEEDCOLUMNCOUNT - 1) + " TEXT)");
db.execSQL(sb.toString());

TestCase:

public class ProviderTestSpeed extends ProviderTestCase2<MyProvider> {

    private ContentValues createElementForId(String id) {
        ContentValues cv = new ContentValues();
        for (int i = 0; i < TESTSPEEDCOLUMNCOUNT; ++i) {
            cv.put("C" + i, id);
        }
        return cv;
    }



    public void testSpeed() {
        Log.d(TAG, "testSpeed start columnCount = " + columnCount);
        ArrayList<ContentValues> list = new ArrayList<ContentValues>();
        ContentValues[] tabcv = {};
        for (int j = 0; j < 10; ++j) {
            list.clear();
            for (int i = 0; i < 500; ++i) {
                ContentValues cv = createElementForId(String.valueOf(i));
                list.add(cv);
            }
            mContentResolver.bulkInsert(TestSpeedCONTENT_URI, list.toArray(tabcv));
        }
        Log.d(TAG, "testSpeed insertFinished");
        Cursor cursor = mContentResolver.query(TestSpeedCONTENT_URI, null, null, null, null);
        cursor.moveToFirst();
        Log.d(TAG, "testSpeed itemCount = " + cursor.getCount() + " columnCount=" + cursor.getColumnCount());

        // build the tab to avoid dynamic allocation during the mesure
        ArrayList<String> listColumns = new ArrayList<String>();
        for (int i = 0; i < TESTSPEEDCOLUMNCOUNT; ++i) {
            listColumns.add("C" + i);
        }
        String[] tabColumnsType = {};
        String[] tabColumns = listColumns.toArray(tabColumnsType);

        Date now = new Date();
        long start = now.getTime();
        do {
            for (int i = 0; i < TESTSPEEDCOLUMNCOUNT; ++i) {
                // get the all the columns of the table
                cursor.getString(cursor.getColumnIndex(tabColumns[i]));
            }
        } while (cursor.moveToNext());
        now = new Date();
        long end = now.getTime();

        Log.d(TAG, "testSpeed took " + (end - start) + " with getColumnIndex at each time");
        cursor.moveToFirst();
        now = new Date();
        start = now.getTime();
        do {
            for (int i = 0; i < TESTSPEEDCOLUMNCOUNT; ++i) {
                // get the all the columns of the table using directly the column id
                cursor.getString(i);
            }
        } while (cursor.moveToNext());
        now = new Date();
        end = now.getTime();
        Log.d(TAG, "testSpeed took " + (end - start) + " with getColumnIndex before loop");
    }
}

I think that the performance drop between 200 to 500 come from the cursor window. I had a lot of logs like that above 200 columns:

W/CursorWindow(1628): Window is full: requested allocation 2412 bytes, free space 988 bytes, window size 2097152 bytes

Answer 2

i guess this has nothing to do with android. in SQLite, accessing column through index (column position) is supposed to be faster.