By investigating why some apis are taking too much time to respond I encountered that it's because of the db queries increases as the queryset increases. In Django select related and prefetch related will helps to reduce this problem. Here I am trying to solve such a problem where I used prefetch related in a serializer and still making too much db queries.
models.py
class Order(models.Model):
orderid = models.AutoField(primary_key=True)
order_number = models.CharField(max_length=20, unique=True)
#....
class SubOrder(models.Model):
suborderid = models.AutoField(primary_key=True)
orderid = models.ForeignKey(
Order, on_delete=models.CASCADE, related_name="orderitems", db_column="orderid"
)
order_number = models.CharField(max_length=50)
product_quantity = models.PositiveIntegerField()
#....
views.py
class GetOrderDetailsByStore(mixins.ListModelMixin, generics.GenericAPIView):
def get(self, request, order_number=None, *args, **kwargs):
start = time.time()
logger.info("started %s", start)
store_id = self.kwargs["storeid"]
status_list = [SubOrder.Suborder_Status.PAYMENT_INITIATED,
SubOrder.Suborder_Status.PAYMENT_FAILED,
SubOrder.Suborder_Status.PAYMENT_PENDING,
SubOrder.Suborder_Status.ORDER_INITIATED]
order_items = Order.objects.filter(Q(storeid=store_id) &~Q(order_status__in=status_list)).order_by(
"-created_date"
)
order_items_eager_loading = AllOrderSellerSerializer.setup_eager_loading(
order_items)
serializer = AllOrderSellerSerializer(order_items_eager_loading, many=True).data
end = time.time()
diff = end - start
logger.info("ended %s", end)
logger.info("time taken %s", diff)
return Response({"message": "success", "data": serializer}, status=status.HTTP_200_OK)
serializer.py
class AllOrderSellerSerializer(serializers.ModelSerializer):
orderitems = AllOrderItemsSellerSerializer(many=True)
store_reference = serializers.CharField(source="store_reference.store_reference")
user_reference = serializers.CharField(source="user_reference.user_reference")
number_of_items = serializers.SerializerMethodField("get_number_of_items")
class Meta:
model = Order
fields = ["order_number", "order_request_number", "store_reference",
"user_reference","number_of_items", "total_amount", "orderitems"]
@staticmethod
def get_number_of_items(obj):
list_of_pdt_quantity_of_order = [order_item.product_quantity for order_item in obj.orderitems.filter(orderid=obj.orderid)]
total_ordered_products_quantity = sum(list_of_pdt_quantity_of_order)
return total_ordered_products_quantity
# return obj.orderitems.count()
@staticmethod
def setup_eager_loading(queryset):
""" Perform necessary eager loading of data. """
queryset = queryset.prefetch_related('orderitems')
queryset = queryset.select_related('user_reference', 'store_reference')
return queryset
Here the number_of_items field takes the sum of prodcut_quantity. But here the db queries get increased as the orders increases. if this "number_of_items" removed this takes only 2 queries for any number of orders, but because of this field queries get increasing and api taking much time to respond. Does this using prefetch related in a wrong way or is there any way to solve this much of db queries?
