Android SQLite querys are too slow

Question

Good day guys. I have an query in my app which is called very often, and it takes lot of time. My db is encrypted with SQLiteCipher (I know that costs a lof of performance so I call and open DB only once). So actually i have a listview with a custom adapter which lists die name, lastMassage and the time of the lM. So I have to do this work:

public Massage getLastMassage(String ChatNumber) {
    String[] args = {ChatNumber};
    //String selectQuery="SELECT * FROM " + MassageDBHelper.DATABASE_NAME + " WHERE PhoneNumber=?";
    //Cursor cursor=database.rawQuery(selectQuery,args);
    Cursor cursor = database.query(MassageDBHelper.DATABASE_NAME, columns, "PhoneNumber=?", args, null, null, null);

    cursor.moveToLast();
    Massage massage = cursorToMassage(cursor);
    cursor.close();

    return massage;
}

That was the code in my databaseHelperClass called MassageDataSource, I've tried also the query string but there are not differences in Performance. In my CustomAdapter I have to do this for every Chat (every person who was written):

Massage lastMassage = massageDataSource.getLastMassage(kontaktItem.getNummer());
    Date date = lastMassage.getTimeAsDate();
    cal.setTime(date);
    int Jahr = cal.get(Calendar.YEAR);
    int Monat = cal.get(Calendar.MONTH);
    int Tag = cal.get(Calendar.DAY_OF_MONTH);
    int Stunden = cal.get(Calendar.HOUR_OF_DAY);
    int Minuten = cal.get(Calendar.MINUTE);

    if (Jahr < Kalenderjahr || Monat < Kalendermonat || Tag + 1 < Kalendertag) {
        viewHolder.Uhrzeit.setText(String.format("%02d", Tag) + "." + String.format("%02d", Monat) + "." + Jahr % 1000);
    } else if (Tag < Kalendertag) {
        viewHolder.Uhrzeit.setText(R.string.gestern);
    } else {
        viewHolder.Uhrzeit.setText(String.format("%02d", Stunden) + ":" + String.format("%02d", Minuten));
    }

    viewHolder.Kontaktname.setText(kontaktItem.getName());
    viewHolder.Chatsnippet.setText(lastMassage.getMassageText());
    //set Image from server
    return view;

Edit Here is also the cursorToMassage function which is called:

private Massage cursorToMassage(Cursor cursor) {
    int idPhoneNumber = cursor.getColumnIndex(MassageDBHelper.COLUMN_PHONENUMBER);
    int idType = cursor.getColumnIndex(MassageDBHelper.COLUMN_TYPE);
    int idMassagetext = cursor.getColumnIndex(MassageDBHelper.COLUMN_MASSAGETEXT);
    int idAttachment = cursor.getColumnIndex(MassageDBHelper.COLUMN_ATTACHMENTID);
    int idTime = cursor.getColumnIndex(MassageDBHelper.COLUMN_TIME);
    int idPosition = cursor.getColumnIndex(MassageDBHelper.COLUMN_POSITION);


    String phoneNumber = cursor.getString(idPhoneNumber);
    String type = cursor.getString(idType);
    String massagetext = cursor.getString(idMassagetext);
    String attachment = cursor.getString(idAttachment);
    String time = cursor.getString(idTime);
    Boolean position = Boolean.valueOf(cursor.getString(idPosition));

    Massage massage;
    if (type.equals("text")) {
        massage = new Massage(position, massagetext, phoneNumber, time);
    } else {
        massage = new Massage(position, attachment, type, phoneNumber, time);
    }

    return massage;
}

It's a story off topic, but did the Massage class means Message? — Stanley Ko, Mar 06 '19 at 09:48
Yes it means, there are information abput the reciever, time, transmitter, type, text — Jones, Mar 06 '19 at 09:49
I posted answer, and it seems that this is a duplicated question. Please check it. If this is a real duplicated question, I will rise flag. — Stanley Ko, Mar 06 '19 at 09:54
I checked it out and the other question is no help to my problem. — Jones, Mar 06 '19 at 10:00

score 0 · Answer 1 · answered Mar 06 '19 at 09:52

Try transaction. Android Database Transaction

public Massage getLastMassage(String ChatNumber) {
    database.beginTransaction();
    Massage massage = null;
    try {
        String[] args = {ChatNumber};
        //String selectQuery="SELECT * FROM " + MassageDBHelper.DATABASE_NAME + " WHERE PhoneNumber=?";
        //Cursor cursor=database.rawQuery(selectQuery,args);
        Cursor cursor = database.query(MassageDBHelper.DATABASE_NAME, columns, "PhoneNumber=?", args, null, null, null);

        cursor.moveToLast();
        massage = cursorToMassage(cursor);
        cursor.close();


    } catch {
        //Error in between database transaction 
    } finally {
        database.endTransaction();
        return massage;
    }
}

Android SQLite querys are too slow

1 Answers1