jeudi 7 janvier 2016

Optimizing SQLite Inner Join Query - Android

I am working with GTFS and trying to figure out a way to speed up my inner join. The tables "Trips" and "StopTimes" have a common column "TripID" and the tables "StopTimes" and "Stops" have a common column "StopID". The inner join retrieves all Stops that correspond to a certain Trip, however it takes about 20 seconds. Also the "StopTimes" table consists of 9 million rows while the others consists of a couple thousand. The following code is my query.

String joinQuery = "SELECT s.stop_id, s.stop_code, s.stop_name "
+ "FROM Trips as t INNER JOIN StopTimes as st ON     st.trip_id = t.trip_id "
+ "INNER JOIN Stops as s ON s.stop_id = st.stop_id "
+ "WHERE t.trip_id = " + TripID + " AND t.shape_id = " + ShapeID
+ " ORDER BY st.stop_sequence";

This is the code executed upon database creation.

String CREATE_TRIPS_TABLE = "CREATE VIRTUAL TABLE " + TABLE_TRIPS + " USING FTS3("
            + KEY_T_ROUTE_ID + " INTEGER,"
            + KEY_T_NAME + " TEXT,"
            + KEY_TRIP_ID + " INTEGER,"
            + KEY_DIRECTION_ID + " INTEGER,"
            + KEY_SHAPE_ID + " INTEGER);";

    String CREATE_STOPS_TABLE = "CREATE VIRTUAL TABLE " + TABLE_STOPS + " USING FTS3("
            + KEY_STOP_ID + " INTEGER,"
            + KEY_STOP_CODE + " TEXT,"
            + KEY_STOP_NAME + " TEXT,"
            + KEY_STOP_LAT + " DOUBLE,"
            + KEY_STOP_LON + " DOUBLE,"
            + KEY_STOP_WHEELCHAIR + " INTEGER);";

    String CREATE_STOPTIMES_TABLE = "CREATE VIRTUAL TABLE " + TABLE_STOP_TIMES + " USING FTS3("
            + KEY_S_TRIP_ID + " INTEGER,"
            + KEY_S_STOP_ID + " INTEGER,"
            + KEY_ARRIVAL_TIME + " TEXT,"
            + KEY_STOP_SEQUENCE + " INTEGER,"
            + KEY_SHAPE_DIST_TRAVELLED + " DOUBLE);";

I followed a few suggestion I found on here and am still not able to speed up my query, any feedback is appreciated.

Aucun commentaire:

Enregistrer un commentaire