23👍
There are dozens of ways to optimize your database queries with the Django ORM. As usual, the Django documentation is great and has a good list of them. Here’s some quick tips for query optimization:
1) iterator()
If you are accessing the queryset
only once. So for example you can use this as,
traffic = Traffic.objects.all()
for t in traffic.iterator():
...
...
While defining fields of your models
. As the Django documentation says,
This is a number one priority, after you have determined from
profiling what indexes should be added. Use Field.db_index or
Meta.index_together to add these from Django. Consider adding indexes
to fields that you frequently query using filter(), exclude(),
order_by(), etc. as indexes may help to speed up lookups.
Hence you can modify your model as,
class Traffic(Timestamp):
sessions = models.IntegerField(blank=True, null=True, db_index=True)
new_users = models.IntegerField(blank=True, null=True, db_index=True)
reminder = models.IntegerField(blank=True, null=True, db_index=True)
campaigns = models.IntegerField(blank=True, null=True, db_index=True)
new_sales = models.IntegerField(blank=True, null=True, db_index=True)
3) prefetch_related()
or select_related()
If you have relations within your models
, using prefetch_related
or select_related
would be a choice. As per the Django documentation,
select_related
works by creating a SQL join
and including the fields of the related object in the SELECT statement. For this reason, select_related
gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related
is limited to single-valued relationships – foreign key and one-to-one.
prefetch_related
, on the other hand, does a separate lookup for each
relationship, and does the ‘joining’ in Python. This allows it to prefetch
many-to-many and many-to-one objects, which cannot be done using
select_related
, in addition to the foreign key and one-to-one relationships that are supported by select_related
.
select_related
does a join
, prefetch_related
does two separate queries. Using these you can make your queries upto 30% faster.
If your template
design allows you to display results in multiple pages your can use Pagination
.
You also need to understand that the Django Querysets are lazy which means that it won’t query the database untill its being used/evaluated. A queryset in Django represents a number of rows in the database, optionally filtered by a query. For example,
traffic = Traffic.objects.all()
The above code doesn’t run any database queries. You can can take the traffic
queryset and apply additional filters, or pass it to a function, and nothing will be sent to the database. This is good, because querying the database is one of the things that significantly slows down web applications. To fetch the data from the database, you need to iterate over the queryset:
for t in traffic.iterator():
print(t.sessions)
Django Debug Toolbar is a configurable set of panels that display various debug information about the current request/response and when clicked, display more details about the panel’s content. This includes:
- Request timer
- SQL queries including time to execute and links to EXPLAIN each query
Modifying your code: (remember Querysets are Lazy)
traffic = Traffic.objects.all()
totals = traffic.aggregate(Sum('sessions'), Sum('new_users'), Sum('reminder'), Sum('campaigns'), Sum('new_sales'), Sum('sales_renewals'))
total_sessions = totals.get('sessions__sum')
total_new_users = totals.get('new_users__sum')
total_reminder = totals.get('reminder__sum')
total_campaigns = totals.get('campaigns__sum')
total_new_sales = totals.get('new_sales__sum')
total_sales_renewals = totals.get('sales_renewals__sum')
t_2014 = traffic.filter(created__year='2014')
t_sessions_2014_wd2 = t_2014.filter(created__week_day=2).aggregate(Sum('sessions'))
...
...
For Call #1 in template (for loop to display table of all results):
{% for t in traffic.iterator %}
{{ t.sessions }}
...
...
{% endfor %}
3👍
As for Question 1, it shouldn’t be a problem to reuse the queryset from the first call.
traffic = Traffic.objects.all()
totals = traffic.aggregate(Sum('sessions'), Sum('new_users'), Sum('reminder'), Sum('campaigns'), Sum('new_sales'), Sum('sales_renewals'))
This should spare you an additional call to the database.
Regarding Question 2, you can again reuse the queryset from the first call, and filter the year, which gives you a new queryset, e.g.
traffic_2014 = traffic.filter(created__year='2014')
You can then continue filtering the days and aggregating with this new queryset, like you did before, or create new querysets for each day, assuming you aggregate multiple attributes every day, thus saving you another dozen database calls.
I hope this helps you.
- Django import export – Unable to import model with BinaryField
- Exclude URLs from Django REST Swagger
- Django JSONField and searching through the list of dictionaries using ILIKE
2👍
Not addressing the questions directly, but I think you should consider a different approach.
Based on my understanding:
- The view may be requested often.
- The data should change rarely.
- There is a need for complicated data manipulation (summing fields by year, month, day etc.)
There is no need to perform the same queries each time someone requests the view.
Load all the data in one step and perform manipulations inside the view. You can use a library like Pandas and create complicated data sets. The view will be now CPU bound so use a caching system like Redis to avoid recalculating. Invalidate when the data has changed.
Another approach: perform the calculations periodically by using a task queue like Celery and populate Redis.
- What could cause a Django error when debug=False that isn't there when debug=True
- How can I specify the parameter for POST requests while using APIView with django-rest-swagger