Fix N+1 query in buildStopList — replace per-stop GetRouteIDsForStop with batch GetRoutesForStops

[sreenivasivbieb]

Labels: performance, bug

---

Description

buildStopList currently fires a separate GetRouteIDsForStop query for every stop in the loop. If there are 40 stops, that's 40 individual database queries executing back to back.

Current Behavior

func (rb *referenceBuilder) buildStopList(stops []gtfsdb.Stop) {
	rb.stopList = make([]models.Stop, 0, len(stops))

	for _, stop := range stops {
		routeIds, err := rb.api.GtfsManager.GtfsDB.Queries.GetRouteIDsForStop(rb.ctx, stop.ID) // 🔴 fires once per stop
		if err != nil {
			continue
		}

		routeIdsString := rb.processRouteIds(routeIds)
		rb.stopList = append(rb.stopList, rb.createStop(stop, routeIdsString))
	}
}

Query breakdown for 40 stops:

• GetStopsByIDs → 1 query (upstream, batch fetched correctly)
• GetRouteIDsForStop → 40 queries (one per stop inside the loop)
• Total: 41 queries

Expected Behavior

All route IDs for all stops should be fetched in a single batch query using the existing GetRoutesForStops method, which already accepts a slice of stop IDs. A map is then built from the result so that each stop's routes are looked up in memory — no database calls inside the loop.

Query breakdown for 40 stops:

• GetStopsByIDs → 1 query
• GetRoutesForStops → 1 query
• Total: 2 queries

Proposed Fix

func (rb *referenceBuilder) buildStopList(stops []gtfsdb.Stop) {
	rb.stopList = make([]models.Stop, 0, len(stops))

	stopIDs := make([]string, 0, len(stops))
	for _, stop := range stops {
		stopIDs = append(stopIDs, stop.ID)
	}

	routesForStops, err := rb.api.GtfsManager.GtfsDB.Queries.GetRoutesForStops(rb.ctx, stopIDs)
	if err != nil {
		return
	}

	stopRouteMap := make(map[string][]string)
	for _, r := range routesForStops {
		stopRouteMap[r.StopID] = append(stopRouteMap[r.StopID], r.ID)
	}

	for _, stop := range stops {
		routeIdsString := stopRouteMap[stop.ID]
		if routeIdsString == nil {
			routeIdsString = []string{}
		}
		rb.stopList = append(rb.stopList, rb.createStop(stop, routeIdsString))
	}
}

Notes

• GetRoutesForStops is already used in arrivalAndDepartureForStopHandler — no new queries need to be written.
• Verify the field names on the GetRoutesForStops return struct (StopID, ID) match before merging.
• This is a classic N+1 query pattern. Any other reference builder loops that call per-item queries should be audited for the same issue.

[chokoskoder]

hey I saw that this problem wasnt merged properly 2 weeks ago , can I take it up and generate a commit pr for this ?