Queryset Optimization Case Study 1

Today I’m revisit my legacy work that I have done long time a go. I’m developing admin panel that displaying list of product and including display total income that we have got in each items.

Below are my model schema:

class Item(models.Model):
    ...
    brand = models.ForeignKey(Brand)
    owner = models.ForeignKey(User)
    base_price = models.ForeignKey(BasePricing)
    categories = models.ManyToManyField(Category)

    @property
    def total_rental_income(self):
        """Getting total income."""
        return self.booking_set.aggregate(total=Sum('order__fee'))

class Booking(models.Model):
    ...
    item = models.ForeignKey(Item)

class Order(models.Model):
    ...
    booking = models.ForeignKey(Booking)
    fee = models.DecimalField(max_digits=12, decimal_places=2, default=0.00)

Currently I just select the data with simple queryset and display 50 items per page.
For your information, I’m profiling my queryset using django-querycount and django-debug-toolbar.

items = Item.objects.all()

Here is my query count:

|------|-----------|----------|----------|----------|------------|
| Type | Database  |   Reads  |  Writes  |  Totals  | Duplicates |
|------|-----------|----------|----------|----------|------------|
| RESP |  default  |   314    |    0     |   314    |     43     |
|------|-----------|----------|----------|----------|------------|
Total queries: 314 in 40.3557s

And here is the detail duplicate query that captured by django debug toolbar.
screen-shot-2016-12-30-at-5-01-10-pm

Now I realized I have to optimize this queryset. My first attempt to fix this issue are using “select_related” all foreign keys and “prefetch_related” my many to many field to select the data eagerly. So my queryset will load all at once instead of the lazy load (default django orm behavior).

items = Item.objects.all().select_related(
    'brand', 'owner', 'base_price'
).prefetch_related(
    'categories'
)

Here is the query count result after my first optimization.

|------|-----------|----------|----------|----------|------------|
| Type | Database  |   Reads  |  Writes  |  Totals  | Duplicates |
|------|-----------|----------|----------|----------|------------|
| RESP |  default  |    74    |    0     |    74    |     9      |
|------|-----------|----------|----------|----------|------------|
Total queries: 74 in 18.0481s

According the query count result, the query much better than before. 314 reads reduced into 74 reads and was 43 duplicates reduced into 9 duplicates.
I did not attach my django debug toolbar because previous captured queries already disappeared. But there’s still one thing that still duplicate from my model schema above. 50 duplicates in my template.
screen-shot-2016-12-30-at-5-22-10-pm

I just know this, that I called my custom property from “Item” model in my template and this custom property is always evaluated and not affected with our optimization above.

Now, here is my second optimization to fix my aggregation issue. I remove my property in my “Item” model and uses “annotate” in my queryset.

items = Item.objects.all().select_related(
    'brand', 'owner', 'base_price'
).prefetch_related(
    'categories'
).annotate(
    rental_income=Sum('booking__order__fee')
)

# And I call my income calculation within my template loop.
{{ item.rental_income|default:0 }}

And here is my query count result.

|------|-----------|----------|----------|----------|------------|
| Type | Database  |   Reads  |  Writes  |  Totals  | Duplicates |
|------|-----------|----------|----------|----------|------------|
| RESP |  default  |    18    |    0     |    18    |     9      |
|------|-----------|----------|----------|----------|------------|
Total queries: 18 in 11.4635s

Now my queryset way better than before, 74 queries reduced into 18 queries.
I still had 9 duplicate query and I’m still working on it. Maybe i’ll update this post if I can optimize the whole queryset. 😀

References:
https://docs.djangoproject.com/en/dev/ref/models/querysets/#select-related
https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related
https://docs.djangoproject.com/en/dev/topics/db/aggregation/#aggregation

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s