Dmitry

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