Performance: 'stops-for-route' times out (>30s) due to N+1 query pattern #165
Description
I noticed high latency in the stops-for-route endpoint when testing with larger routes (e.g., Sound Transit "1 Line"). Locally, the response time exceeds 30 seconds, which leads to timeouts in clients like Postman.
While some of this delay might be exacerbated by local SQLite locking, profiling suggests there is an N+1 query pattern that could be optimized to improve performance for larger agencies.
Observed Behavior:
Client side(Postman):
Server logs:
Root Cause:
Upon profiling internal/restapi/stops_for_route_handler.go, I identified an N+1 query bottleneck in the buildStopsList function.
The code iterates through every stop ID associated with the route and executes synchronous database queries inside the loop. For a route with N stops, this results in 2N database round-trips per request.
Additional Finding: In stopsForRouteHandler, the error for time.LoadLocation is currently ignored in the same file.
Proposed Solution:
- Refactoring buildStopsList: Implement a bulk SQL query (e.g., GetStopsByIDs) to fetch all stop metadata in a single database call.
- Fixing Error Handling: Add a nil check for time.LoadLocation to default to UTC if the system timezone is missing, preventing server panics.
I have a local branch ready to work on this. Please assign this issue to me.