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