android - "Optimizing"在Android中访问游标 : Position vs Column names

Question

从性能的角度来看:如果在每次访问我的游标时我都使用类似这样的东西是不是很好:

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

或者如果我改用它，我应该会看到性能的可衡量改进:

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

我更喜欢第一种方法，因为其余代码不依赖于列在查询中的位置，而只依赖于列的名称。

是否值得为了使用常量位置访问游标的“性能改进”而牺牲它？

Answer 1

为了回答您的问题(顺便说一下我的问题)，我做了一些测试。

这个测试基本上是为了检查这两种情况的查询花费了多少时间:

1. 使用 cursor.getString(cursor.getColumnIndex(COLUMN_NAME)) 方法
2. 先获取列id，然后直接调用cursor.getString(COLUMN_POSITION)方法

为了使性能测试有意义，我在数据库中插入了 5000 行，然后我进行了一个查询，将我的 ContentProvider 放在这些元素上。

结果:

 ___________________________________________________________________________
| 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%         |
|___________________________________________________________________________|

因此，首先获取列 ID 并直接调用 getString() 方法将花费大约 20% 的时间。

---

测试方法详情:

平台:Android 4.3 上的 Nexus 7 (2012)

数据库创建:

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

测试用例:

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

我认为 200 到 500 之间的性能下降来自光标窗口。我有很多超过 200 列的日志:

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