android - Sqlite 数据库打开缓慢

Question

我正在处理字典应用程序，并且有一个数据库，其中包含一个超过 100,000 行的表。我在带有 ListView 的 fragment 之一中使用此数据库，每次打开 fragment 时，显示数据大约需要 4-5 秒。有可能加快速度吗？或者至少让数据库只打开一次，这样下次我打开 fragment 时就不会花费太多时间。我考虑过在新线程中打开它，但它会花费相同的时间，不是吗？这是 fragment 的代码:

private EditText mEditText;
private ListView mWordsListView;
private WordsListAdapter mAdapter;
private List<Words> mWordsList;

private static final String KEY_ENGLISH = "english";
private static final String KEY_ARABIC = "arabic";
private String query = " ";
View view = inflater.inflate(R.layout.content_main, container, false);

    database = new MyDatabase(getActivity());

    mEditText = (EditText)view.findViewById(R.id.mSearch);
    mWordsListView = (ListView)view.findViewById(R.id.mWordsList);

    mWordsList = database.getWordsList(query);
    mAdapter = new WordsListAdapter(mWordsList, inflater, getActivity());
    mWordsListView.setAdapter(mAdapter);

    mWordsListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

            Words mWords = (Words) mAdapter.getItem(position);
            Intent intent = new Intent(getActivity(), WordActivity.class);
            intent.putExtra(KEY_ENGLISH, mWords.getWord_eng());
            intent.putExtra(KEY_ARABIC, mWords.getWord_arab());
            startActivity(intent);

        }
    });

数据库代码:

public class MyDatabase extends SQLiteAssetHelper{
SQLiteDatabase db;

private static final String DATABASE_NAME = "dict.sqlite";
private static final int DATABASE_VERSION = 1;

private static final String TABLE_DATA = "data";
private static final String TABLE_PROPERTIES = "properties";
public static final String COLUMN_WORD = "word";
public static final String COLUMN_SHORT = "short";



public MyDatabase(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    db = getWritableDatabase();
}

public List<Words> getWordsList(String query){
    List<Words> wordsList = new ArrayList<>();

    String[] columns = {"idx", COLUMN_WORD, COLUMN_SHORT, "long", "wordid", "roots", "syn", "see", "tok", "phon", "ant"};
    String whereClause = null;
    String[] whereArgs = null;
    Cursor cursor = db.query(TABLE_DATA, columns, whereClause, whereArgs, null, null, null);
    while(cursor.moveToNext()){
        Words words = new Words();
        words.setWord_eng(cursor.getString(cursor.getColumnIndex("word")));
        words.setWord_arab(cursor.getString(cursor.getColumnIndex("short")));
        wordsList.add(words);
    }
    return wordsList;
}

日志:

03-03 02:36:45.949 19500-19500/net.idey.arabicdictionary I/InputMethodManager: windowGainedFocus, mServedView=android.support.v7.widget.AppCompatEditText@41f96f50, inputType=0x1, softInputMode=0x110, pid=19500
03-03 02:36:48.469 19500-19500/net.idey.arabicdictionary I/SQLiteAssetHelper: successfully opened database dict.sqlite
03-03 02:36:48.729 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 96 bytes, window size 2097152 bytes
03-03 02:36:49.799 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 6 bytes, window size 2097152 bytes
03-03 02:36:50.249 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 59 bytes, window size 2097152 bytes
03-03 02:36:50.749 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 89 bytes, window size 2097152 bytes
03-03 02:36:51.509 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 45 bytes, window size 2097152 bytes
03-03 02:36:52.119 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 12 bytes, window size 2097152 bytes
03-03 02:36:52.819 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 19 bytes, window size 2097152 bytes
03-03 02:36:53.579 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 11 bytes, free space 1 bytes, window size 2097152 bytes
03-03 02:36:54.409 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 59 bytes, window size 2097152 bytes
03-03 02:36:55.519 19500-19500/net.idey.arabicdictionary W/CursorWindow: Window is full: requested allocation 132 bytes, free space 122 bytes, window size 2097152 bytes
03-03 02:36:56.309 19500-19500/net.idey.arabicdictionary I/Choreographer: Skipped 471 frames!  The application may be doing too much work on its main thread.

Answer 1

查询表中的所有条目时速度较慢

您可以使用分页并在需要时加载条目。

1. 在第一个查询中仅检索第一个 10/15 项(来自后台任务)
2. 在用户 checkout 前 10/15 项或当用户滚动列表底部时触发另一个查询作为后台任务

检查LIMTI 和OFFSET 子句的用法 here