Geek Answers Handbook

Reduce Django Database Queries

I have a very large dataset and is growing, and I need to create many filters, but it will get out of control soon and was hoping that someone could help me combine some of the requests into one call. Below is my opinion.

Call # 1 - for a loop to display a table of all results

traffic = Traffic.objects.all()

Call # 2 - Combined total amount request

totals = Traffic.objects.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')

Call # 3, # 4, # 5, # 6, etc. - To filter the database by month and day of the week

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=2).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=3).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=4).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=5).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=6).aggregate(Sum('sessions'))

The problem is that I need to create a few dozen filters, because I have 3 years of data with several data points per column, for which we need the amount for.

Questions:

  • Can I combine call # 1 into call # 2
  • Call # 2 №3, , , ?

, - . . .

class Timestamp(models.Model):
    created = models.DateField()

    class Meta:
        abstract = True


class Traffic(Timestamp):
    sessions = models.IntegerField(blank=True, null=True)
    new_users = models.IntegerField(blank=True, null=True)
    reminder = models.IntegerField(blank=True, null=True)
    campaigns = models.IntegerField(blank=True, null=True)
    new_sales = models.IntegerField(blank=True, null=True)
    sales_renewals = models.IntegerField(blank=True, null=True)

    # Meta and String
    class Meta:
        verbose_name = 'Traffic'
        verbose_name_plural = 'Traffic Data'

    def __str__(self):
        return "%s" % self.created
+4
3

ORM Django. , Django . :

1) iterator()

queryset . , , ,

traffic = Traffic.objects.all()

for t in traffic.iterator():
    ...
    ...

2) db_index=True

models. Django,

, . Field.db_index Meta.index_together, Django. , filter(), exclude(), order_by() .., .

,

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() select_related()

models, prefetch_related select_related . Django,

select_related , SQL join SELECT. select_related . , , "" , select_related - -.

prefetch_related, , , "" Python. "--" "--", select_related, , select_related.

select_related join, prefetch_related . , 30% .

4) Django Pagination

template , Pagination.

5) Querysets Lazy

, Querysets Django , , , / . Django , . ,

traffic = Traffic.objects.all()

. traffic , . , - , -. , :

for t in traffic.iterator():
    print(t.sessions)

6) django-debug-toolbar

Django Debug , /, . :

  • SQL-, , EXPLAIN

: (, Querysets 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'))
...
...

# 1 ( ):

{% for t in traffic.iterator %}
    {{ t.sessions }}
    ...
    ...
{% endfor %}
+5

1, .

traffic = Traffic.objects.all()
totals = traffic.aggregate(Sum('sessions'), Sum('new_users'), Sum('reminder'), Sum('campaigns'), Sum('new_sales'), Sum('sales_renewals'))

.

2, , , .

traffic_2014 = traffic.filter(created__year='2014')

, , , , , , .

, .

+1

, , .

:

  • .
  • .
  • ( , , ..).

, - .

. Pandas . , , Redis, . .

Another approach: periodically perform calculations using a task queue such as Celery and populate Redis.

+1
source

More articles: