Django pagination work very slow when Dataset is too large

Question

Django Pagination is very slow when dataset is very large(>100k) my dataset is 80 columns and more than 100k rows. when i load the page or change the page it takes 14-15 second Is there anything that i can do to make it fast
what can i do to make it fast. please help with this

model.py

class Finaltest3(models.Model):
a = models.DateTimeField(db_column='Dat',primary_key=True, blank=True, null=False)  # Field name made lowercase.
b = models.TextField(db_column='C', blank=True, null=True)  # Field name made lowercase.
c = models.TextField(db_column='S', blank=True, null=True)  # Field name made lowercase.
d = models.TextField(db_column='Ca', blank=True, null=True)  # Field name made lowercase.
e = models.TextField(db_column='Ac', blank=True, null=True)  # Field name made lowercase.
f = models.TextField(db_column='Re', blank=True, null=True)  # Field name made lowercase.
 .
 .
 .
 .
 .
fd = models.TextField(db_column='Add', blank=True, null=True)  # Field name made lowercase.
fd= models.TextField(db_column='Addit', blank=True, null=True)  # Field name made lowercase.
fd = models.TextField(db_column='Additional', blank=True, null=True)  # Field name made lowercase.

class Meta:
    managed = False
    db_table = 'finaltest3'

view.py

from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
import datetime
from datetime import date
from datetime import timedelta
from django.http import HttpResponseRedirect
from django.shortcuts import render
def displayLogs(request):
  today = date.today() 
  lastweek=today - timedelta(days=7)
  print('lastweek',lastweek)    
  user_list = Finaltest3.objects.filter(dat__gt=lastweek)
  paginator = Paginator(user_list, 10)
  page = request.GET.get('page')
  users = paginator.get_page(page)
return render(request,'displayLogs.html',{'users':users})

template.html

<div class="pagination" style="border-style:inset;border-width:5px;padding-bottom: 5px">
{% if users.has_previous %}

<a class="pagination-action" href="?page=1"> <i class="fas fa-angle-double-left"></i></a>

<a class="pagination-action" href="?page={{ users.previous_page_number}}"> <i class="fas fa-angle-left"></i></a>

{% endif %}

{% for num in users.paginator.page_range %}
    {% if users.number == num %}

        <span class="pagination-number pagination-current"><strong>{{ num }}</strong></span>

    {% elif num > users.number|add:'-3' and num < users.number|add:'3' %}

    <a class="pagination-number" href="?page={{ num }}">{{num}}</a>

    {% endif %}

{% endfor %}

{% if users.has_next %}

<a class="pagination-action" href="?page={{ users.next_page_number }}"><i class="fas fa-angle-right"></i></a>
<a class="pagination-action" href="?page={{ users.paginator.num_pages }}"> <i class="fas fa-angle-double-right"> </i></a>

{% endif %}

</div>
<tbody>
                        <tr class="gradeX">
                            {% for item in users %}
                                <td>{{item.dat}}</td>
                                <td>{{item.act}}</td>
                                <td>{{item.actor}}</td>
                                <td class="center">{{item.target}}</td>
                            </tr>

                        {% endfor %}
</tbody>

The only efficient way to optimize code is to first profile it to find where the bottlenecks are (it's a well-known fact that except for a couple very obvious cases we human are very bad at guessing where bottlenecks are). Use a profiler (ie https://github.com/someshchaturvedi/customizable-django-profiler), check the results, and if necessary come back for help once you get those results. — bruno desthuilliers, Mar 15 '19 at 09:46
Install and check your query with https://django-debug-toolbar.readthedocs.io/en/latest/. Also please reference to the system on which this is running. Maybe the system need more RAM or something else. — Tobit, Mar 15 '19 at 23:48
Basically it is quite comman. I think 2 ways to optimize it. `1`, get a batter database (2 core cpu => 16 core cpu, 8 gb mem => 64 gb mem). `2`, get your query to optimize it (how many join, which column to order by) — tim, Mar 16 '19 at 22:56

score 1 · Answer 1 · answered Mar 15 '19 at 11:40

I think the slowness is due to the following:

Probably no database index on the field you're filtering - the DB system you're using has to sort out all data for the lastweek field filtering
Remember that you can print the query Django is generating: print(Finaltest3.objects.filter(dat__gt=lastweek).query).
You're fetching all columns for the data, maybe you can limit the fields to those you'll need - look at only and defer

Django pagination work very slow when Dataset is too large

1 Answers1