inefficient iterarion over Django queryset-CodePudding

I'm trying to speed up the response time of a get request from React to Django. The delay is due to the iteration over a filtered queryset.

stop_time_next_hour = StopTime.objects.filter(
        stop_id=stop_id,
        # Get all arrival times in the next hour
        arrival_time__gte=current_time.time(),
        arrival_time__lte=(current_time   timedelta(hours=1)).time(),
    )

This is the queryset, which must be iterated over, and with each iteration, a request is sent to the DB leading to slow response times.

for stop_time in stop_time_next_hour:
    if stop_time.trip.service_id  == str(service_id):
            check_trip_for_update(gtfsDict,stop_time.trip.trip_id)

The filter is dynamic and is dependent on user input, so the queryset cannot be loaded prior to the get request containing the user input.

How can this performance be optimized?

CodePudding user response：

Just add service_id to the filter

stop_time_next_hour = StopTime.objects.filter(
    trip__service_id=service_id,
    stop_id=stop_id,
    # Get all arrival times in the next hour
    arrival_time__gte=current_time.time(),
    arrival_time__lte=(current_time   timedelta(hours=1)).time(),
)

And you can optimize it even more with select_related or prefetch related. For example:

stop_time_next_hour = StopTime.objects.filter(
    trip__service_id=service_id,
    stop_id=stop_id,
    # Get all arrival times in the next hour
    arrival_time__gte=current_time.time(),
    arrival_time__lte=(current_time   timedelta(hours=1)).time(),
).select_related("trip") <-- depends on your db structure

If there is just one instance with this service_id then you can add .first() at the end.

You need to share more details for further optimization