3 способа сравнения: 1. таблица со списком, 2. список со списком, 3. таблица с таблицей.
2 способ самый быстрый (20-40мс для 100 тыс. элементов). 1й способ такой же как и 2й, но дольше т.к. чтение из БД при сравнении. 3 способ медленнее 2го, но быстрее первого при сравнении. Но если учитывать, что нужно в начале положить элементы во вторую таблицу - в сумме может быть медленнее первого способа.
public void test(final String table, final HashPair[] items) {
final ArrayList<String> list_for_update = new ArrayList<>();
final ArrayList<String> list_for_delete = new ArrayList<>();
final long start_timer = System.currentTimeMillis();
final SQLiteDatabase db = getReadableDatabase();
Log.d(TAG, "test start");
Cursor cursor = null;
try {
cursor = db.rawQuery(String.format(Locale.ENGLISH, "SELECT * FROM %s ORDER BY uid ASC;", table), null);
int index = 0;
while (cursor.moveToNext()) {
final String uid = cursor.getString(1);
final long hash = cursor.getLong(2);
while (index < items.length) {
final HashPair item = items[index];
final int compare = uid.compareTo(item.uid);
if (compare < 0) {
list_for_delete.add(uid);
break;
}
++index;
if (compare == 0) {
if (hash != item.hash) {
list_for_update.add(uid);
}
break;
}
list_for_update.add(uid);
}
}
} catch (final Exception ex) {
ex.printStackTrace();
} finally {
if (cursor != null) {
cursor.close();
}
}
Log.d(TAG, String.format(Locale.ENGLISH, "test finish: %d ms, count: %d, delete: %d", (System.currentTimeMillis() - start_timer), list_for_update.size(), list_for_delete.size()));
}
public void test2(final HashPair[] items1, final HashPair[] items2) {
final ArrayList<String> list_for_update = new ArrayList<>();
final ArrayList<String> list_for_delete = new ArrayList<>();
final long start_timer = System.currentTimeMillis();
Log.d(TAG, "test2 start");
try {
int index1 = 0;
int index2 = 0;
final int len1 = items1.length;
final int len2 = items2.length;
while (index1 < len1) {
final HashPair item1 = items1[index1];
while (index2 < len2) {
final HashPair item2 = items2[index2];
final int compare = item1.uid.compareTo(item2.uid);
if (compare < 0) {
list_for_delete.add(item1.uid);
break;
}
++index2;
if (compare == 0) {
if (item1.hash != item2.hash) {
list_for_update.add(item1.uid);
}
break;
}
list_for_update.add(item1.uid);
}
++index1;
}
} catch (final Exception ex) {
ex.printStackTrace();
}
Log.d(TAG, String.format(Locale.ENGLISH, "test2 finish: %d ms, count: %d, delete: %d", (System.currentTimeMillis() - start_timer), list_for_update.size(), list_for_delete.size()));
}
public void test3() {
final SQLiteDatabase db = getReadableDatabase();
final long start_timer = System.currentTimeMillis();
Log.d(TAG, "test3 start");
Cursor cursor = null;
try {
db.execSQL("DELETE FROM table1 where uid in (SELECT table1.uid FROM table1 LEFT OUTER JOIN table2 ON table1.uid = table2.uid where table2.uid is null);");
} catch (final Exception ex) {
ex.printStackTrace();
}
Log.d(TAG, "delete finish: " + (System.currentTimeMillis() - start_timer));
int items_for_update = 0;
try {
cursor = db.rawQuery("SELECT * FROM table2 LEFT OUTER JOIN table1 ON table2.uid = table1.uid where table1.uid is null or (table1.hash <> table2.hash);", null);
items_for_update = cursor.getCount();
while (cursor.moveToNext()) {
final String uid = cursor.getString(1);
final long hash = cursor.getLong(2);
// Log.d(TAG, String.format(Locale.ENGLISH, "for update %s %d", uid, hash));
}
} catch (final Exception ex) {
ex.printStackTrace();
} finally {
if (cursor != null) {
cursor.close();
}
}
Log.d(TAG, String.format(Locale.ENGLISH, "test3 finish: %d ms, count: %d", (System.currentTimeMillis() - start_timer), items_for_update));
}
public static class HashPair implements Comparable<HashPair> {
public final String uid;
public final long hash;
public HashPair(final String uid, final long hash) {
this.uid = uid;
this.hash = hash;
}
@Override
public int compareTo(@NonNull final HashPair rhs) {
return uid.compareTo(rhs.uid);
}
}
