I have a BIG transactions table. I want to to find a specific field(card number) in transactions inserted in last one minute, Therefore it is not reasonable to search the entire table. So i want to search just in top 20 rows.
Here is the my code:
public boolean isCardTapedInLastMinute(String date, String time,String UID) {
String oneMinuteBefore = getOneMinuteBefore(time);
String tables = TRX.TABLE_NAME;
String[] columns = {TRX._ID};
String selection = TRX.COLUMN_NAME_TRX_DATE + " = ? AND " +
TRX.COLUMN_NAME_TRX_TIME + " >= ? AND " +
TRX.COLUMN_NAME_CARD_ID + " = ?";
String[] selectionArgs = {date, oneMinuteBefore, UID};
String sortOrder = TRX.COLUMN_NAME_TRX_DATE
+ BusDBContract.SORT_ORDER_DESC
+ ", "
+ TRX.COLUMN_NAME_TRX_TIME
+ BusDBContract.SORT_ORDER_DESC;
String limit = "1";
Cursor cursor = query(selection, selectionArgs, columns, tables, sortOrder, limit);
if (null == cursor) {
return false;
}
if (!cursor.moveToFirst()) {
cursor.close();
return false;
}
return true;
}
and the query method:
private Cursor queryWith(String selection, String[] selectionArgs, String[] columns, String tables, String sortOrder, String limit) {
SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(tables);
Cursor cursor;
try {
cursor = builder
.query(
mBusOH.getReadableDatabase(),
columns,
selection,
selectionArgs,
null,
null,
sortOrder,
limit);
} catch (SQLException e) {
Log.e(TAG, "[query]sql_exception: " + e.getMessage());
return null;
}
return cursor;
}
It is working correctly and it searches the entire table. It takes about 50-100 ms to be performed for 15000 rows but it may be bigger and i want to optimize it by searching in top 20 rows.
What is the best way to do so?
Aucun commentaire:
Enregistrer un commentaire