aba2s
aba2s

Reputation: 522

How to optimize Django API Query

How can I optimize my api View or Serializer ? It takes more than 3 minutes to perform the task. I think it due to foreign key fields but even I retrieve them on the serializer fields, it still run slowly.

api/View.py

@api_view(['GET', 'POST'])
def edge_list(request):
    """
    List all edges of all networks, or create a new edge.
    """
    if request.method == 'GET':
        edges = Edge.objects.all()
        context = {'request': request}  # for filtering by field in url
        serializer = EdgeSerializer(edges, many=True, context=context)
        return Response(serializer.data)

    elif request.method == 'POST':
        serializer = EdgeSerializer(data=request.data)
        if serializer.is_valid():
            serializer.save()
            return Response(serializer.data, status=status.HTTP_201_CREATED)
        return Response(serializer.error, status=status.HTTP_400_BAD_REQUEST)

api/serializers.py

class EdgeSerializer(DynamicFieldsMixin, serializers.ModelSerializer):

    class Meta:
        model = Edge
        fields = (
            'id', 'edge_id', 'name', 'length', 'speed',
            'lanes', 'param1', 'param2', 'param3', 'network',
            'road_type', 'source', 'target'
            )

api/models.py

class Edge(models.Model):
    network = models.ForeignKey(RoadNetwork, on_delete=models.CASCADE)
    source = models.ForeignKey(Node, related_name='source',
                               on_delete=models.CASCADE,
                               help_text='Source node of the edge',
                               )
    target = models.ForeignKey(Node, related_name='target',
                               on_delete=models.CASCADE,
                               help_text='Target node of the edge',
                               )
    road_type = models.ForeignKey(RoadType, on_delete=models.CASCADE,
                                  help_text='Roadtype of the edge'
                                  )
    edge_id = models.PositiveBigIntegerField(db_index=True)
    name = models.CharField(max_length=80, blank=True, help_text='Name of the edge')
    geometry = models.LineStringField()
    length = models.FloatField()
    speed = models.FloatField(null=True, blank=True)
    lanes = models.SmallIntegerField(null=True, blank=True)
    param1 = models.FloatField(null=True, blank=True)
    param2 = models.FloatField(null=True, blank=True)
    param3 = models.FloatField(null=True, blank=True)

    def __str__(self):
        return self.name or 'Edge {}'.format(self.edge_id)

Upvotes: 1

Views: 345

Answers (1)

AKX
AKX

Reputation: 169388

Yep, you're doing N*3+1 queries.

Instead of

edges = Edge.objects.all()

try

edges = Edge.objects.select_related('network', 'source', 'target').all()

to fetch all of those foreign key fields in one query.

You can also try

edges = Edge.objects.prefetch_related('network', 'source', 'target').all()

for three queries (with possibly less redundant data).

Upvotes: 3

Related Questions