Django Slow Database Model Query

Question

I am having issues with the performance of a couple of queries in my Django app... all others are very fast.

I have an Orders model with OrderItems, the query seems to be running much slower than other queries (1-2 seconds, vs. 0.2 seconds). I'm using MySQL backend. In the serializer I do a count to return whether an order has food or drink items, I suspect this is causing the performance hit. Is there a better way to do it?

Here is my models setup for Order and OrderItems

class Order(models.Model):
  STATUS = (
       ('1', 'Placed'),
       ('2', 'Complete')
   )
   PAYMENT_STATUS = (
       ('1', 'Pending'),
       ('2', 'Paid'),
       ('3', 'Declined'),
       ('4', 'Manual')

   )
   shop= models.ForeignKey(Shop,on_delete=models.DO_NOTHING)
   customer = models.ForeignKey(Customer,on_delete=models.DO_NOTHING)
   total_price = models.DecimalField(max_digits=6, decimal_places=2,default=0)
   created_at = models.DateTimeField(auto_now_add=True, null=True)
   time_completed = models.DateTimeField(auto_now_add=True, null=True,blank=True)
   time_cancelled = models.DateTimeField(auto_now_add=True, null=True,blank=True)
   status = models.CharField(max_length=2, choices=STATUS, default='1',)
   payment_method =  models.CharField(max_length=2, choices=PAYMENT_METHOD, default='3',)
   payment_status =  models.CharField(max_length=2, choices=PAYMENT_STATUS, default='1',)
   type =  models.CharField(max_length=2, choices=TYPE, default='1',)

   def __str__(self):
       return str(self.id)


class OrderItem(models.Model):
   order = models.ForeignKey(Order,on_delete=models.CASCADE)
   type = models.CharField(max_length=200,default='DRINK')
   drink = models.ForeignKey(
       Drink,
       blank=True,null=True,on_delete=models.DO_NOTHING
   )
   food = models.ForeignKey(
       Food,
       blank=True,
       null=True,
        on_delete=models.DO_NOTHING
   )
   quantity = models.IntegerField(blank=True,null=True)
   price = models.DecimalField(max_digits=6, decimal_places=2,default=0)
   created_at = models.DateTimeField(auto_now_add=True, null=True)
   delivered = models.BooleanField(default=False)

   def __str__(self):
       return str(self.id)

In my rest order serializer, here is the query for get,

queryset = Order.objects.filter(shop=shop,status__in=['1','2'],payment_status__in=['2','4'])

The serializer is below, but this query is quite slow. I assume because I am doing a count() on OrderItems - is there a more efficient way to do this?

class OrderOverviewSerializer(serializers.ModelSerializer):
   tabledetails = serializers.SerializerMethodField()
   has_food = serializers.SerializerMethodField()
   has_drink = serializers.SerializerMethodField()


   class Meta:
       model = Order
       fields = ['id','total_price', 'created_at','has_food','has_drink','type','status','shop','table','customer','shopdetails']

   def get_shopdetails(self, instance):
       qs = Shop.objects.get(id=instance.shop.id)
       serializer = ShopSerializer(instance=qs, many=False)
       return serializer.data


   def get_has_food(self, obj):
       foodCount = OrderItem.objects.filter(order=obj.id,type='FOOD').count()
       return foodCount

   def get_has_drink(self, obj):
       drinkCount = OrderItem.objects.filter(order=obj.id,type='DRINK').count()
       return drinkCount

Answer 1

There reason for that is famous N+1 problem that Django ORM is so inept to handle. The solution for it is to use select_related answerd in this question. More on that here.

Answer 2

• You should consider db_index=True on the fields you're querying over (Order.status, Order.payment_status, OrderItem.type).
• get_shopdetails() isn't used for anything in the serializer? (On a similar note, the getter for tabledetails is missing... are you maybe presenting some code that's not exactly what you're running?)
  • get_shopdetails() is redundant anyway; you can simply declare shop = ShopSerializer() and DRF will know what to do.
• If the get_has_food/get_has_drink fields did prove to be the bottleneck (which they apparently didn't), you could use a Django aggregate to count the rows during the query for orders.
• Speaking of, your serializer is accessing several foreign keys, which will all cause N+1 queries; you can add .select_related('shop', 'customer', 'table') (or .prefetch_related() the same) at the very least to have those get loaded in one fell swoop.

Beyond this -- profile your code! The easiest way to do that is to copy the skeleton from manage.py and add some code to simulate your query, e.g. (this is dry-coded):

import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "my_settings")
import django
django.setup()
from django.test import TestClient
c = TestClient()
for x in range(15):
   c.get("/api/order/")  # TODO: use correct URL

and run your script with

python -m cProfile my_test_script.py

You'll see which functions end up taking the most time.