mercredi 2 mars 2016

Sqlite database opening slowly

I'm working on dictionary application and have a database that has a table with over 100,000 rows. I use this database in one of fragments with listview and each time I open the fragment, it takes about 4-5 seconds to display data. Is it possible to speed it up? Or at least to make database open only once so that the next times I open fragment it won't take too much time. I thought about opening it in new thread, but it'll take the same amount of time, won't it? Here's code for fragment:

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

        }
    });

Code for database:

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

logcat:

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.

Aucun commentaire:

Enregistrer un commentaire