Java Android SQLite 和庞大的数据集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1407442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Android SQLite and huge data sets
提问by MattC
We are creating an app for a client that has hundreds of megabytes of HTML in SQLite databases. We have implemented a way to query this data and scroll through it all in a reasonably fast manner. The issue is that some of the databases have very large queries (20,000+ rows) and we are seeing errors when we are growing the queries as the user is scrolling. So I guess the question is, what options do we have in querying and displaying tens of thousands of rows of data in Android?
我们正在为在 SQLite 数据库中有数百兆字节 HTML 的客户端创建一个应用程序。我们已经实现了一种查询这些数据并以相当快的方式滚动浏览的方法。问题是一些数据库有非常大的查询（20,000 多行），当我们在用户滚动时增加查询时，我们会看到错误。所以我想问题是，我们在 Android 中查询和显示数万行数据有哪些选择？
Here is the stacktrace we're seeing:
这是我们看到的堆栈跟踪：
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
Here is our CursorAdapter code:
这是我们的 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());
}
}
采纳答案by CommonsWare
what options do we have in querying and displaying tens of thousands of rows of data in Android?
我们在Android中查询和显示数万行数据有哪些选择？
You mean besides telling you that reading 20,000+ rows on a 3.5" LCD is bat-guano crazy? ;-)
你的意思是除了告诉你在 3.5" LCD 上阅读 20,000 多行是蝙蝠鸟粪疯了吗？;-)
It looks like CursorWindow, which is used somewhere under the covers, is having issues managing >17,000 rows. That could be one of two things:
看起来CursorWindow，在幕后某处使用的 ，在管理 > 17,000 行时遇到问题。这可能是两件事之一：
- You are out of heap space. With a 16MB non-compacting heap, and the fact that a
Cursorholds the entire result set in the heap, that is not out of the question. CursorWindowonly supports 1MB of data, which is what the error message suggests more directly.
- 您的堆空间不足。使用 16MB 非压缩堆，并且 a
Cursor将整个结果集保存在堆中这一事实并非不可能。 CursorWindow只支持 1MB 的数据，这是错误信息更直接的暗示。
If there is a logical way to divide your queries into discrete chunks, you could do incremental queries and use CursorJoinerto stitch them together, and see if that helps.
如果有一种合乎逻辑的方法将您的查询划分为离散的块，您可以进行增量查询并将CursorJoiner它们拼接在一起，看看是否有帮助。
But, in all seriousness, 20,000+ rows in a 3.5" screen, on a device that most closely resembles a 12-year-old PC in horsepower, is really asking a lot.
但是，严肃地说，在 3.5 英寸屏幕上显示 20,000 多行，在马力上与 12 岁的 PC 最相似的设备上，确实要求很高。
回答by Aurelian Cotuna
If you really need that you can also split your data and read chunks like this:
如果你真的需要，你也可以像这样拆分数据和读取块：
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();
It's working under 2 s for 5k rows if you have indexed table.
如果您有索引表，它可以在 2 秒内处理 5k 行。
Thanks, Arkde
谢谢，阿克德
回答by John61590
In my experience, limiting queries makes it take a lot longer to get results because starting new cursors is expensive for low limits. I tried doing 62k rows with 1k and even 10k limit just now and it is very slow and unusable since I have to start more than 6 cursors. I'll just stick with not supporting 2.3.3.... It is the latest build that I get CursorWindow ERROR instead of WARN.
根据我的经验，限制查询会使获得结果需要更长的时间，因为启动新游标对于低限制来说很昂贵。我刚刚尝试用 1k 甚至 10k 限制做 62k 行，但它非常慢且无法使用，因为我必须启动 6 个以上的游标。我会坚持不支持 2.3.3 ......这是我得到 CursorWindow ERROR 而不是 WARN 的最新版本。
Here is what I did though. This is probably the best algorithm I think. Don't have to do queries twice etc. However, it can get pretty slow with big queries and small limits so you have to test out what works best a lot. In my case, it's not fast enough for my purposes since it doesn't handle 62k rows well.
不过，这就是我所做的。这可能是我认为最好的算法。不必进行两次查询等。但是，大查询和小限制可能会变得非常慢，因此您必须测试哪些最有效。就我而言，它对于我的目的来说不够快，因为它不能很好地处理 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
}
