Как эффективно выполнять вложенные запросы SQLlite

Question

У меня есть база данных (футбольных) игр, содержащая дочерние таблицы периодов (например, первая и вторая половина), события (например, цель, предупреждение) и места (где вы были до и во время игры).

Чтобы отобразить родительскую таблицу игр, я использую CursorLoader с соответствующими аргументами, например так:

    public Loader<Cursor> onCreateLoader(final int id, final Bundle args) {
    ...
    if ((mGamesDB.isOpen()) && (id == GAMES_CURSOR_ID)) {
        return createGamesCursorLoader();
    }
    return null;
}

    private Loader<Cursor> createGamesCursorLoader() {
    //Because we don't want to create a ContentProvider for now, we use the technique suggested here:
    //https://stackru.com/questions/18326954/how-to-read-an-sqlite-db-in-android-with-a-cursorloader
    return new CursorLoader(getBaseContext(),null, GamesContract.Games.PROJECTION,
            null, null, GamesContract.Games.ORDER_BY) {
        @Override
        public Cursor loadInBackground() {
            if (mGamesDB.isOpen()) {
                return mGamesDB.query(
                    GamesContract.Games.TABLE_NAME,
                    GamesContract.Games.PROJECTION,
                    null, null,
                    null, null,
                    GamesContract.Games.ORDER_BY
                );
            }
            else return null;
        }
    };
}

Это все работает отлично. Однако, как только я начинаю перебирать курсор Игры (когда вызывается onLoadFinished), мне нужно создать подзапросы для Периодов, Событий и Местоположений, используя текущий GameID. Итак, я делаю:

    private Game buildGameFromDB(final Cursor gameCursor) {
    if (!mGamesDB.isOpen() || (gameCursor == null) || gameCursor.isClosed() ) return null;
    final WatchGame game = new WatchGame(gameCursor.getString(GamesContract.Games.COLUMN_ID_INDEX),
            gameCursor.getLong(GamesContract.Games.COLUMN_ACTUAL_START_MILLIS_INDEX),
            gameCursor.getLong(GamesContract.Games.COLUMN_ACTUAL_END_MILLIS_INDEX),
            gameCursor.getInt(GamesContract.Games.COLUMN_HOME_TEAM_COLOR_INDEX),
            gameCursor.getInt(GamesContract.Games.COLUMN_AWAY_TEAM_COLOR_INDEX),
            gameCursor.getInt(GamesContract.Games.COLUMN_HOME_TEAM_SCORE_INDEX),
            gameCursor.getInt(GamesContract.Games.COLUMN_AWAY_TEAM_SCORE_INDEX));

    //FIXME: Ugly nested queries on the main UI thread
    final String[] periodsWhereArgs = {game.getmGameID()};
    final Cursor periodsCursor = mGamesDB.query(GamesContract.Periods.TABLE_NAME, GamesContract.Periods.PROJECTION,
                                                GamesContract.Periods.WHERE, periodsWhereArgs,
                                                null, null, GamesContract.Periods.ORDER_BY);
    while (periodsCursor.moveToNext()) {
        final Period period = new Period(
                periodsCursor.getInt(GamesContract.Periods.COLUMN_PERIOD_NUM_INDEX),
                periodsCursor.getLong(GamesContract.Periods.COLUMN_ACTUAL_START_MILLIS_INDEX),
                periodsCursor.getLong(GamesContract.Periods.COLUMN_ACTUAL_END_MILLIS_INDEX),
                periodsCursor.getFloat(GamesContract.Periods.COLUMN_START_BATTERY_PCT_INDEX),
                periodsCursor.getFloat(GamesContract.Periods.COLUMN_END_BATTERY_PCT_INDEX),
                periodsCursor.getString(GamesContract.Periods.COLUMN_GOOGLE_ACCOUNT_NAME_INDEX),
                periodsCursor.getInt(GamesContract.Periods.COLUMN_NUM_LOCATIONS_INDEX),
                periodsCursor.getInt(GamesContract.Periods.COLUMN_NUM_LOCATIONS_IN_FIT_INDEX),
                periodsCursor.getInt(GamesContract.Periods.COLUMN_CALORIES_INDEX),
                periodsCursor.getInt(GamesContract.Periods.COLUMN_STEPS_INDEX),
                periodsCursor.getInt(GamesContract.Periods.COLUMN_DISTANCE_METRES_INDEX),
                periodsCursor.getLong(GamesContract.Periods.COLUMN_WALKING_MILLIS_INDEX),
                periodsCursor.getLong(GamesContract.Periods.COLUMN_RUNNING_MILLIS_INDEX),
                periodsCursor.getLong(GamesContract.Periods.COLUMN_SPRINTING_MILLIS_INDEX)
        );
        game.addPeriod(period);
    }
    periodsCursor.close();
...

Хотя количество игр и периодов не будет большим (возможно, 100 с), может быть 50 событий на игру и 2000 локаций на игру.

Как я могу сделать это более эффективно? Возможности, которые возникают у меня:

1. Большой многосоединительный запрос, который я затем должен разобрать. Я очень доволен этим типом SQL, если предположить, что SQLite справится с ним эффективно. Мне это не нравится в основном потому, что периоды, события, местоположения и дочерние таблицы позволяют мне эффективно денормализовать и создать гигантский беспорядок.
2. Расширяя мои selectionArgs для периодов, событий и т. Д., Чтобы получить динамический список из 10 или 100 игр, которые у меня есть
   3. Каким-то образом улучшая эффективность того, что у меня есть, и превращая их в асинхронные запросы

Любые советы или указатели приветствуются.

Answer 1

Вы думаете, что столкнулись с проблемой N+1 SELECT, когда вы выполняете много запросов и, таким образом, снижаете производительность из-за всей дополнительной связи между вашим приложением и сервером базы данных.

На самом деле это не так: SQLite является встроенной базой данных, поэтому отдельного сервера не существует, и многие небольшие запросы также эффективны.

Но есть и другие вещи, которые вы можете сделать, чтобы ускорить запросы:

• Добавьте правильные индексы: столбцы, которые используются для поиска строк, должны быть проиндексированы; см. Планирование запросов для деталей. Ограничения PRIMARY KEY или UNIQUE автоматически создают индексы для своих столбцов; для других столбцов вы должны создать индекс (ы) самостоятельно.

  В этом случае идентификатор игры в таблице периодов должен иметь индекс.

• Загружать меньше данных, т.е. загружать данные только тогда, когда это действительно необходимо. Самый простой способ сделать это - удалить объекты игры / периода и запустить интерфейс непосредственно из базы данных. Это потребует изменения архитектуры всего приложения и может оказаться невозможным, если ваши объекты на самом деле выполняют больше обработки, чем просто хранят данные.

Обратите внимание, что оба вышеуказанных пункта работают независимо от того, какой тип запроса (N+1 или пакетный или объединенный) вы используете.

Перенос доступа к базе данных в отдельный поток не ускоряет их, он просто позволяет пользователю взаимодействовать с пользовательским интерфейсом, пока данные все еще загружаются. (Полезен ли пользовательский интерфейс без всех данных - это другой вопрос.) И снова асинхронный поток будет работать с любыми запросами.