Django Simple Query very slow at thousand data.

[Eric Kiser]

Okay so I've used django on two of my projects with the following criteria:

Hosted @: PythonAnywhere

Database: MySQL and SQLite

PROBLEM: At first with a few hundred data the query is okay, but when I am querying 2,000 of data it takes 3 minutes to load it on a simple table. This is not normal because I've read that a thousand data shouldn't be a problem.

Here are my codes:

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, no ordering even coz I'm told sorting takes another hit on the database.

- ask help from the guys at pythonanywhere thinking I might not have enough workers, they told me there's no problem with my account.

- shift from SQLite to MySQL and still the same.

Help would really be nice, thank you.

[Motaz Hejaze]

1 - Sqlite is very slow when its compared to other sql databases like Mysql and Postgresql ..

2 - You need to use pagination , you are viewing using templates and not generating an excel or csv file to pass all this data in one shot

3 - pythonanywhere.com basic account comes with limited resources , you may look for alternatives , or request a higher plan

4 - if you adjust some mysql parameters you might have faster querying but you may sacrifice another features so dont play with them unless you know what you are doing

5 - consider use caching for the second request 

--
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/813e339d-e9ce-45f9-a391-a497ae5d3857%40googlegroups.com.

[Aldian Fazrihady]

Using the way you specify model in your view, the resulting query won't be efficient.
On each loop in your template, the foreign keys will be automatically queried, which is the cause of the slowness.

You need to override the get_queryset method of your view and generate the ORM query there.
In the ORM query, please add select_select related to the foreign keys.

--
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/813e339d-e9ce-45f9-a391-a497ae5d3857%40googlegroups.com.

--

Regards,

Aldian Fazrihady
http://aldianfazrihady.com