How to do nested SQLlite queries efficiently

Question

I have a database of (Soccer) games, containing child tables of periods (e.g. first and second half), events (e.g. a goal, a caution), and locations (where you were prior to and during the game).

To display the parent Games table, I'm using a CursorLoader with the appropriate arguments like this:

    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://stackoverflow.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;
        }
    };
}

That all works fine. However, once I start iterating through the Games cursor (when onLoadFinished is called), I need to create subqueries for Periods, Events, and Locations using the current GameID. So I do:

    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();
...

Although the number of games and periods won't be large (maybe 100s), there could be 50 events per game, and 2000 locations per game.

How can I do this more efficiently? Possibilities that occur to me are:

1. A large multi-join query which I then have to sort through. I'm very comfortable with that type of SQL, assuming SQLite will handle it efficiently. I don't like this mostly because the periods, events, and locations and child tables so I'd effectively be denormalizing and creating a giant mess.
2. Expanding my selectionArgs for periods, events etc. to be a dynamic list of 10 or 100 games I have
   3. Somehow improving the efficiency of what I have and turning these into Async queries

Any advice or pointers appreciated.

Answer 1

You think you are running into the N+1 SELECT problem, where you are executing many queries and thus reduce performance because of all the additional communication between your application and the database server.

Actually, that is not the case: SQLite is an embedded database, so there is no separate server, and many small queries are just as efficient.

But there are other things that you can do to speed up queries:

• Add proper indexes: Column(s) that are used to look up rows should be indexed; see Query Planning for details. PRIMARY KEY or UNIQUE constraints automatically create indexes on their columns; for other columns, you have to create the index(es) yourself.

  In this case, the game ID in the periods table should have an index.

• Load less data, i.e., load data only when actually needed. The simplest way to do this would be to remove your game/period objects, and to run the UI directly from the database. This would require changing the architecture of the entire app, and might not be feasible if your objects actually do more processing than just storing data.

Please note that both points above work regardless of what kind of query (N+1 or batched or joined) you use.

Moving database accesses into a separate thread does not speed them up, it just can allow the user to interact with the UI while the data is still being loaded. (Whether the UI is useful without all the data is another question.) And, again, an asynchronous thread would work with any kind of query.