我们正在为一个客户端创建一个应用程序,该应用程序在SQLite数据库中有数百兆字节的超文本标记语言。我们已经实现了一种方法来查询这些数据,并以一种相当快的方式滚动所有这些数据。问题是一些数据库有非常大的查询(20,000+行),当我们在用户滚动时增加查询时,我们会看到错误。所以我想问题是,在Android中查询和显示数万行数据时,我们有什么选择?
下面是我们看到的堆栈跟踪:
09-10 19:19:12.575: WARN/IInputConnectionWrapper(640): showStatusIcon on inactive InputConnection
09-10 19:19:18.226: DEBUG/dalvikvm(640): GC freed 446 objects / 16784 bytes in 330ms
09-10 19:19:32.886: ERROR/CursorWindow(19416): need to grow: mSize = 1048576, size = 36, freeSpace() = 30, numRows = 17717
09-10 19:19:32.896: ERROR/CursorWindow(19416): not growing since there are already 17717 row(s), max size 1048576
09-10 19:19:32.916: ERROR/CursorWindow(19416): The row failed, so back out the new row accounting from allocRowSlot 17716
09-10 19:19:33.005: ERROR/Cursor(19416): Failed allocating fieldDir at startPos 0 row 17716
09-10 19:19:35.596: DEBUG/Cursor(19416): finish_program_and_get_row_count row 24315
09-10 19:19:41.545: DEBUG/dalvikvm(698): GC freed 2288 objects / 126080 bytes in 260ms
09-10 19:19:43.705: WARN/KeyCharacterMap(19416): No keyboard for id 0
09-10 19:19:43.717: WARN/KeyCharacterMap(19416): Using default keymap: /system/usr/keychars/qwerty.kcm.bin
09-10 19:20:04.705: ERROR/CursorWindow(19416): need to grow: mSize = 1048576, size = 17, freeSpace() = 3, numRows = 17094
09-10 19:20:04.716: ERROR/CursorWindow(19416): not growing since there are already 17094 row(s), max size 1048576
09-10 19:20:04.726: ERROR/Cursor(19416): Failed allocating 17 bytes for text/blob at 17093,2
09-10 19:20:05.656: DEBUG/Cursor(19416): finish_program_and_get_row_count row 5257
09-10 19:24:54.685: DEBUG/dalvikvm(637): GC freed 9297 objects / 524176 bytes in 247ms
09-10 19:32:07.656: DEBUG/dalvikvm(19416): GC freed 9035 objects / 495840 bytes in 199ms
下面是我们的CursorAdapter代码:
private class MyAdapter extends ResourceCursorAdapter {
public MyAdapter(Context context, Cursor cursor) {
super(context, R.layout.my_row, cursor);
}
public void bindView(View view, Context context, Cursor cursor) {
RowData data = new RowData();
data.setName(cursor.getInt(cursor.getColumnIndex("name")));
TextView tvItemText = (TextView)view.findViewById(R.id.tvItemText);
tvItemText.setText(data.getName());
view.setTag(data);
}
@Override
public Cursor runQueryOnBackgroundThread(CharSequence constraint) {
/* Display the progress indicator */
updateHandler.post(onFilterStart);
/* Run the actual query */
if (constraint == null) {
return myDbObject.getData(null);
}
return myDbObject.getData(constraint.toString());
}
}
发布于 2012-03-27 15:20:03
如果你真的需要这样做,你也可以像这样拆分你的数据并读取块:
int limit = 0;
while (limit + 100 < numberOfRows) {
//Compose the statement
String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100";
//Execute the query
Cursor cursor = myDataBase.rawQuery(statement, null);
while (cursor.moveToNext()) {
Product product = new Product();
product.setAllValuesFromCursor(cursor);
productsArrayList.add(product);
}
cursor.close();
limit += 100;
}
//Compose the statement
String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ (numberOfRows - limit)+"', 100";
//Execute the query
Cursor cursor = myDataBase.rawQuery(statement, null);
while (cursor.moveToNext()) {
Product product = new Product();
product.setAllValuesFromCursor(cursor);
productsArrayList.add(product);
}
cursor.close();
如果你有索引表,它可以在2秒内处理5k行。
谢谢,Arkde
发布于 2013-07-18 07:55:45
根据我的经验,限制查询会花费更长的时间来获得结果,因为对于较低的限制,启动新游标的代价很高。我刚才试着用1k甚至10k的限制来做62k行,它非常慢并且不可用,因为我必须启动6个以上的游标。我只会坚持不支持2.3.3....这是最新的版本,我得到的是CursorWindow错误而不是警告。
以下是我所做的。我认为这可能是最好的算法。不需要做两次查询等。然而,对于大查询和小限制,它可能会变得相当慢,所以你必须测试出什么是最好的。在我的例子中,它不够快,因为它不能很好地处理62k行。
int cursorCount = 0;
int limit = 1000; //whatever you want
while (true)
{
Cursor cursor = builder.query(mDatabaseHelper.getReadableDatabase(), columns, selection, selectionArgs, null, null, null, Integer.toString(limit));
if (cursor == null) {
return null;
} else if (!cursor.moveToFirst()) { //if it is empty, return null
return null;
}
cursorCount = cursor.getCount();
if (cursorCount % limit != 0)
{
return cursor;
}
limit+=1000; //same amount as the one above
}
https://stackoverflow.com/questions/1407442
复制相似问题