Django's ORM is handy and convenient in a way that developers don't have to focus on the implementation under the hood. You can use the little dots to retrieve all related objects, such as foo.bar.tar.hello.kittie.. However, understanding what's going on under the scene helps you build efficient applications.
I recently had a chance to optimize an event report using
Django debug toolbar. I dumped a production database to my local for testing. This is the before-after result:
|
# of Queries |
Page Load Time(ms) |
CPU Time(ms) |
| Before Optimization |
5698 |
3444 |
51156 |
| After Optimization |
16 |
58 |
958 |
Probably nobody has ever realized that a report with only159 entries can hit database 5698 times. That's crazy talk! Yes, blame the little dots (or not paying attention to what a little dot can do, to be precise).
For the page load time and CPU time, of course, this is measured on my local. The production server can be much faster.
If you calculate the percentage decrease, the # of queries, page load time and CPU are 99.72%, 98.32% and 98.13%, respectively.
So, how did I optimize it?
My goal was to reduce both # of database hits and CPU time. So basically, all the effort was to prevent the "expensive dots":
- Eliminated the unnecessary queries.
- Used aggregate functions instead of looping through all objects to calculate the totals.
- Used select_related with care.
- Selected only the fields needed with values_list.
1) Eliminated the unnecessary queries.
Only small percentage of our events has the addons. But when looping through the registrants, each registrant queries the database for registered addons regardless of whether or not the event even has any addons. So this was taken care of by adding the if statement to check if an event has any addons. Notice that we didn't call
event.has_addons. Instead, we stored the
event.has_addons to
event_has_addons because
has_addons is a function and every time it gets called, a database query will be issued.
- {% if event_has_addons %}
- {% with registrant.registration.regaddon_set.all as regaddons %}
- {% if regaddons %}
- .....
- {% endif %}
- {% endwith %}
- {% endif %}
Now, look at two lines below to see the difference.
- {% if registrant.user_id %}
They look similar, right? But the first one can be expensive - It needs to query the database to get the user object. While the second registrant already has the user_id available. If all you need is the user id, then use
registrant.user_id instead of
registrant.user or
registrant.user.id.
2) Used aggregate functions instead of looping through all objects to calculate the totals.
Originally, the total and balance was calculated by looping through each registration like this:
- # get total and balance (sum)
- for reg8n in registrations:
- if not reg8n.canceled: # not cancelled
- if reg8n.invoice != None:
- if roster_view != 'paid':
- total_sum += float(reg8n.invoice.total)
- balance_sum += float(reg8n.invoice.balance)
Notice reg8n.canceled, the canceled is not a field of reg8n. In order to get the value of reg8n.canceled, it goes through each registrant to check if they have the cancel_dt. Very inefficient. And each reg8n.invoice needs to query the database for invoice as well.
- @property
- def canceled(self):
- """
- Return True if all registrants are canceled. Otherwise False.
- """
- registrants = self.registrant_set.all()
- for registrant in registrants:
- if not registrant.cancel_dt:
- return False
- return True
So, rather than a canceled function, a
canceled field was added to the reg8n. With the
canceled field, I was able to use the aggregate function with only one query.
- # Get the total_sum and balance_sum.
- totals_d = Registration.objects.filter(
- event=event,
- canceled=False
- ).aggregate(
- total_sum=Sum('invoice__total'),
- balance_sum=Sum('invoice__balance') )
- total_sum = totals_d['total_sum']
- balance_sum = totals_d['balance_sum']
3) Used select_related with care.
As you know, you can use select_related to reduce number of database queries. However, reducing the number of database queries doesn't necessarily improve the performance. It would do the opposite sometimes when the tables doing the joins are hugely big. For that reason, select_related was used selectively.
4) Selected only the fields needed with values_list.
The values_list was used heavily when optimizing the report. It helped retrieving the field values of the custom registration form for each registrant with only one query and very little CPU time. Same with the pricing.
After done all of those, although very few probably would care about a faster report , I know, our servers will thank me :-).
