java - Android SQLite 查询太慢

Question

大家好。我的应用程序中有一个查询，该查询经常被调用，并且需要很多时间。我的数据库是用 SQLiteCipher 加密的(我知道这会降低性能，所以我只调用并打开数据库一次)。所以实际上我有一个带有自定义适配器的 ListView ，其中列出了模具名称、lastMassage 和 lM 的时间。所以我必须做这项工作:

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

这是我的数据库HelperClass中名为MassageDataSource的代码，我也尝试过查询字符串，但性能没有差异。在我的 CustomAdapter 中，我必须为每个聊天(每个被写入的人)执行此操作:

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;

编辑这里也是被调用的cursorToMassage函数:

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

Answer 1

尝试交易。 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;
    }
}