Django View, foreign key field is killing performance-CodePudding

MyObjects definition:

class MyObjects(models.Model):
    field_a = models.TextField()
    field_b = models.TextField()
    ...more irrelevant text/int fields
    foreign_key_field = models.ForeignKey(AnotherModel, null=True, on_delete=models.SET_NULL)

View definition:

@api_view(["GET"])
@permission_classes([IsAuthenticated])
def my_endpoint(request):
    objs = MyObjects.objects.filter(is_active=True)

    ...irrelevant logic

    
    my_dict = defaultdict(int)
    for my_obj in objs:
        ...bunch of irrelevant logic
        my_dict[str(my_obj.foreign_key_field)]  = 1
        ...bunch of irrelevant logic

   return Response(my_dict, status=status.HTTP_200_OK)

I do some calculations in my view that are irrelevant and my view takes around 3 seconds for 5000 objects if my_dict[str(my_obj.foreign_key_field)] = 1 is commented out. However, when that line uncommented, my view takes 20seconds. It must be because this field is a foreign key field as none of my other fields are foreign keys. That specific line is the only line referencing that field. How am I able to improve performance on this? I am surprised that just adding this line decreases my performance by that much, as objects is only around 5k and the foreign key table is around 50 objects.

No other operations are touching this dictionary because that specific line, so it's not a cascading affect. If I comment out all logic besides that specific line

@api_view(["GET"])
@permission_classes([IsAuthenticated])
def my_endpoint(request):
    objs = MyObjects.objects.filter(is_active=True)

    my_dict = defaultdict(int)
    for my_obj in objs:
        my_dict[str(my_obj.foreign_key_field)]  = 1
   return Response(my_dict, status=status.HTTP_200_OK)

performance is still awful. Any ideas on how to improve?

CodePudding user response：

you could optimize the query by one of the following steps:

1. Use select_related to reduce the number of database queries

objs = MyObjects.objects.filter(is_active=True).select_related('foreign_key_field')

2. Use prefetch_related to reduce the number of database queries

objs = MyObjects.objects.filter(is_active=True).prefetch_related('foreign_key_field')

3. Use annotate to count objects in a single query

objs = MyObjects.objects.filter(is_active=True).annotate(count=Count('foreign_key_field'))

then

my_dict = {str(obj.foreign_key): obj.count for obj in objs} 
return Response(my_dict, status=status.HTTP_200_OK)