Django query of a few data (1k-3k) is taking too long (minutes)

Question

Okay I have a django app with the following conditions:

Hosted @: PythonAnywhere
Database: SQLite and MySQL

PROBLEM: At first with few hundreds of data its work okay, but when I reach a few thousands (3k) it takes too long to load it on a simple table.

MY CODE:

models.py

class Outgoing(models.Model):
    base_in = models.ForeignKey('warehouse.Incoming', related_name='out', on_delete = models.SET_NULL, null=True)
    trans_date = models.DateField('Date', default=timezone.now)
    trans_type = models.CharField('Type', max_length=50, choices = OUTGOING_TYPE)
    form_no = models.CharField('FORM No', max_length=20, default=0)
    project_site = models.ForeignKey(ProjectSite, related_name='out_project_site', null=True, on_delete = models.SET_NULL)
    released_by = models.ForeignKey(User, related_name='out_released_by', default='', on_delete = models.SET_NULL, null=True)
    released_to = models.ForeignKey(User, related_name='out_released_to', blank=True, null=True, on_delete = models.SET_NULL)
    released_out = models.ForeignKey(Outsider, related_name='outsider_released_to', blank=True, null=True, on_delete = models.SET_NULL)
    unit = models.ForeignKey(UnitProfile, related_name='user_unit', blank=True, null=True, on_delete = models.SET_NULL)

    quantity = models.DecimalField('Quantity', db_index=True, max_digits=20, decimal_places=2, default=0)
    details = models.CharField('Details', max_length=200, default='')
    attachment = models.FileField('Form', upload_to='incoming_form', blank=True)

    create_date = models.DateTimeField('Date Created', auto_now_add=True)

    def __str__(self):
        return "%s" %(self.trans_date)

    class Meta:
        verbose_name = 'Outgoing'
        verbose_name_plural = 'Outgoings'

views.py

class OutgoingView(ListView):
    model = Outgoing
    template_name = 'warehouse/outgoing_page.html'
    context_object_name = 'all_out'

outgoing_page.html

                                <tbody>
                                    {% for outgoing in all_out%}
                                    <tr>
                                        <td class="text-truncate">{{ outgoing.trans_date }}</td>
                                        <td class="text-truncate">{{ outgoing.trans_type }}</td>
                                        <td class="text-truncate">{{ outgoing.form_no }}</td>
                                        <td class="text-truncate info">{{ outgoing.base_in.item }}</td>
                                        <td class="text-truncate danger">{{ outgoing.quantity|intcomma }}</td>
                                        <td class="text-truncate">{{ outgoing.project_site }}</td>
                                        <td class="text-truncate">{{ outgoing.unit }}</td>
                                        <td class="text-truncate">{{ outgoing.released_by }}</td>
                                        <td class="text-truncate">{{ outgoing.released_to }}</td>
                                        <td class="text-truncate">{{ outgoing.released_out }}</td>
                                        <td class="text-truncate">{{ outgoing.details }}</td>
                                        <td class="text-truncate">
                                            <i class="la la-pencil font-medium-3"></i>
                                        </td>
                                    </tr>
                                    {% endfor %}
                                </tbody>

What I did: - simplified my views the best I can. Did not even do any simple sorting as I've read that hits the database again. - Shift from SQLite to MySQL - Asked help from the guys at pythonanywhere thinking I might not have enough workers, they said my account is good.

What puzzles me is that even when I try to load them via admin page, its still very slow.

Any help would be great, thanks.

score 2 · Answer 1 · answered Feb 07 '20 at 15:45

You can consider adding pagination. ListView provides pagination out of the box. Check their example here: https://docs.djangoproject.com/en/3.0/topics/pagination/#paginating-a-listview

So, in your case, it would be

class OutgoingView(ListView):
    model = Outgoing
    template_name = 'warehouse/outgoing_page.html'
    context_object_name = 'all_out'
    paginate_by = 2

And in the view

<tbody>
{% for outgoing in all_out %}
<tr>
      <td class="text-truncate">{{ outgoing.trans_date }}</td>
      <td class="text-truncate">{{ outgoing.trans_type }}</td>
      <td class="text-truncate">{{ outgoing.form_no }}</td>
      <td class="text-truncate info">{{ outgoing.base_in.item }}</td>
      <td class="text-truncate danger">{{ outgoing.quantity|intcomma }}</td>
      <td class="text-truncate">{{ outgoing.project_site }}</td>
      <td class="text-truncate">{{ outgoing.unit }}</td>
      <td class="text-truncate">{{ outgoing.released_by }}</td>
      <td class="text-truncate">{{ outgoing.released_to }}</td>
      <td class="text-truncate">{{ outgoing.released_out }}</td>
      <td class="text-truncate">{{ outgoing.details }}</td>
      <td class="text-truncate">
           <i class="la la-pencil font-medium-3"></i>
      </td>
 </tr>
{% endfor %}

<div class="pagination">
<span class="step-links">
    {% if page_obj.has_previous %}
        <a href="?page=1">&laquo; first</a>
        <a href="?page={{ page_obj.previous_page_number }}">previous</a>
    {% endif %}

    <span class="current">
        Page {{ page_obj.number }} of {{ page_obj.paginator.num_pages }}.
    </span>

    {% if page_obj.has_next %}
        <a href="?page={{ page_obj.next_page_number }}">next</a>
        <a href="?page={{ page_obj.paginator.num_pages }}">last &raquo;</a>
    {% endif %}
</span></div>

I'm using datatables on my template which is where the pagination is done. With your suggestion, how is the data queried? is it by batch? — eakdev, Feb 07 '20 at 15:53

score 2 · Answer 2 · answered Feb 07 '20 at 20:57

This is a problem with the matter in which the queryset is being looped through.

Each foreign key (project_site, released_by, released_to, released_out, unit) is being fetched on each iteration of the loop.

As a summation:

# First loads all objects in queryset
{% for outgoing in all_out%}
   ...
   # will issue query
   # select * from ProjectSite where id=outgoing.project_site_id
   <td class="text-truncate">{{ outgoing.project_site }}</td>

   # will issue query
   # select * from UnitProfile where id=outgoing.unit_id
   <td class="text-truncate">{{ outgoing.unit }}</td>

   ... for all related model information being rendered from foreign key relationships

This problem is summarized by the n+1 query problem and can be avoided by using Django's select_related queryset functionality.

Within your view, you can override the ListView default get_queryset function, and select the related models to the one you are listing like so:

class OutgoingView(ListView):
    model = Outgoing
    template_name = 'warehouse/outgoing_page.html'
    context_object_name = 'all_out'

    def get_queryset(self):
        return Outgoing.objects.select_related('project_site', 'released_by', 'released_to', 'released_out', 'unit')

Which will cause these related models to be joined when the initial query is fired off ( on for outgoing in all_out), and a significant performance increase with only one query to populate the template.

I just updated my code based on your solution. Condition: 17,938 of data Here are the results: Previous it took: 5.3 minutes After updating code: 4.2 minutes Its still very slow — eakdev, Feb 08 '20 at 05:39
@Boban after reading the n+1 issue and select related, does the same logic applies if I query like this? `context['all_in'] = Incoming.objects.annotate(tot_out=Sum('out__quantity'))\ .annotate(current_count=F('quantity')-F('tot_out'))` Does this mean this is not the most efficient way to query? — eakdev, Feb 08 '20 at 06:39
Albeit a lot of data, that still seems very long. Is pagination possible, or will it not make sense in your use case. [Paginating a ListView](https://docs.djangoproject.com/en/3.0/topics/pagination/#paginating-a-listview) will allow you to return a designated number of model objects at a time. We can also have django log any queries to console, in case we've missed a query in our join, or if other N+1 problems are occurring: [Logging query docs](https://docs.djangoproject.com/en/2.2/topics/logging/#examples) — nickswiss, Feb 10 '20 at 15:13

score 0 · Answer 3 · answered Feb 07 '20 at 15:48

Most of the record fields you used in template (for example this one <td class="text-truncate">{{ outgoing.released_by }}</td>) are Foreign Key fields and what is happening here is that for each Outgoing record Django fetches each record referrenced through FK with all fields loaded to call __str__ method on them (on related records, not fields). In your case it means +6 queries for each hit for Outgoing record. This is an n+1 problem.

You may consider using select related on thous FK's with specifying fields you actually need, without invoking __str__ method of related records.

Django query of a few data (1k-3k) is taking too long (minutes)

3 Answers3