Django optimizing Database queries

In this article I'll talk about how to optimize database queries using Django ORM. 

Before, we start, please note that Django queriesests are lazy. And we can use this for good.

Let's take an example. Suppose, we have a model named Bill, and we execute follwing code from any view.

bills = Bill.objects.all()
unpaid_bills = bills.filter(status='UNPAID')

Till now, Django ORM will not hit database at all, means no query is executed yet. 

Django will hit DB only when we evaluate the Queryset. So, when we'll start iterating through this Queryset, either in tempalte or in view, Django will hit DB and make quereis.

Have a look at following example Django template code:

<table>
  <tbody>
  {% for bill in unpaid_bills %}
    <tr>
      <td>{{ bill.id }}</td>
      <td>{{ bill.description }}</td>
      <td>{{ bill.status }}</td>
    </tr>
  {% endfor %}
  </tbody>
</table>

Here everything is all good. Django will make Only one query to DB. 

Let's take anothe case, where models are related to each other through ForeignKey, OneToOneField or ManyToManyField.

For instance, let's assume our Bill model is related to Vendor model through ForeignKey. 

Here is how our model looks now:

class Bill(models.Model):
    description = models.CharField(max_length=255)
    status = models.CharField(max_length=10)
    vendor = models.ForeignKey(Vendor)

Now, if we want to iterate through all unpaid bills with their respective Vendors, Django will execute an extra query for each row of unpaid bills. 

Take a look at new Django template code:

<table>
  <tbody>
  {% for bill in unpaid_bills %}
    <tr>
      <td>{{ bill.id }}</td>
      <td>{{ bill.description }}</td>
      <td>{{ bill.status }}</td>
      <td>{{ bill.vendor.name }}</td>
    </tr>
  {% endfor %}
  </tbody>
</table>

That's very bad. Right.?

 Hitting this much DB is not a good thing, specially when we already know what do we want to retrive at all.

Like in this case we already know, we want to fetch all the vendors whose bills are unpaid. Why not do that in one query ?

Let's make Django do it.

We have select_related method to acheive this. Using this method, we can retrive all related objects in a single query. 

So, instead of filtering unpaid bills, we'll do it like this:

bills = Bill.objects.all()
unpaid_bills = bills.select_related('vendor').filter(status='UNPAID')

Now, Django will prefetch all the Vendors, so, whenever we'll evaulate Queryset extra queries to DB will not done. This will dramatically improve performance of your app.