Android SQLiteDatabase SELECT query take long time

Question

I have about 1500 records in TABLE_CONTACT. My code to get contacts:

public ArrayList<SBContact> getAllContacts() {
    SQLiteDatabase database = dbHelper.getWritableDatabase();
    ArrayList<SBContact> mContacts = new ArrayList<SBContact>();
    String selectQuery = "SELECT  * FROM " + SBDatabaseHelper.TABLE_CONTACT;
    Cursor cursor = database.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            SBContact result = new SBContact(cursor.getString(cursor.getColumnIndex(SBDatabaseHelper.CONTACT_NUMBER)),
                    cursor.getString(cursor.getColumnIndex(SBDatabaseHelper.CONTACT_NAME)), cursor.getInt(cursor.getColumnIndex(SBDatabaseHelper.EXITS_USER)));
            mContacts.add(result);
        } while (cursor.moveToNext());
    }
    return mContacts;
}

The problem is: The first time I run this method, it take about 15ms. 5 seconds later I run this method again, it takes about 20 seconds.

In similar way, after the first call, 15s later, run method again, it take about 10 seconds. And after 2 minutes later, run this method again, it take about 15ms as the first time run.

In this time, no other thread make a read/write query to database.

I don't understand what the SQLiteDatabase does. Does it need time to release memory or do something?

EDITED Sorry all, this is not the problem of SQLiteDatabase. See my answer to more detail about this case.

Answer 1

Close your cursor after each query

Answer 2

Sorry all, my mistake.

This problem does not relate with SQLiteDatabase. It due to AsyncTask problem.

My case is: I run a AsyncTask to get contact from local database (sqlite), and at same time, I start new thread works with server (call api take a long time) in this AsyncTask. When I run the AsyncTask to get contacts again (with execute method), the old AsyncTask still running, and it prevent new one run. That's the reason the new task take a long time to finish. I have fix by use executeOnExecutor method, and the result of method return immediately.

One again, sorry all.