Django query with few data(1,000-3,000) takes too long(1min) to load

[Eric Kiser]

I created an app with the following details:

Hosting: PythonAnywhere

Database: SQLite and MySQL

Problem: When I load the data in a table it takes minutes to load it. My data hasn't even reached a million its just a few thousands.

How I did it:

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 daily_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, I did not even do any sorting as I'm told that hits the database again.

- shift from SQLite to MySQL still the same problem.

- asked help from the guys at pythonanywhere thinking I might not have enough workers, but they said there's no problem with my account

Can someone help me please. Thank you

[Stephen J. Butler]

You've got a lot of foreign key fields where the fetch is being deferred until template render time, and it being done individually for each row instead of in bulk. I think if you added this attribute to your ListView subclass you'd see better performance:

queryset = Outgoing.objects.select_related('base_in', 'project_site', 'released_by', 'released_to', 'released_out', 'unit')

Some of those might be better done as prefetch_related() if their cardinality is low.

Also, although I doubt it's a major factor in your performance, it would be better to write `str(self.trans_date)` instead of using the more convoluted `'%s' % (self.trans_date,)`

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1f3f70bd-d2f6-41cf-a964-70d9ff68e45c%40googlegroups.com.