My datatable using js in django is very slow when i load more than 50000 rows

Question

My Model:

class PartyMaster(models.Model):
    party_name = models.CharField(max_length=200,null=True, blank=True)
    email = models.EmailField(null=True, blank=True)
    contact = models.CharField(max_length=100,null=True, blank=True)
    address = models.CharField(max_length=350,null=True, blank=True)
    country = models.CharField(max_length=30,null=True, blank=True)
    sub_country = models.ForeignKey(CountryCategory,max_length=200,on_delete=models.CASCADE,null=True,blank=True,related_name='party_sub_country')
    state = models.CharField(max_length=50,null=True, blank=True)
    city = models.CharField(max_length=50,null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.party_name + " "+ self.email

My View: In My Model there are 50 000+ rows

def party_list(request):
    parties = PartyMaster.objects.all()
    context = {
        'parties':parties,
    }
    return render(request,'party_master/party_edit.html',context)

My Template:

{% extends 'base.html' %}
{% block title %} Party List's {% endblock %}

{% block body %}

<div class="container">
    <a href="{% url 'add_party' %}" class="btn btn-primary button_class mt-5">Party Master Form</a>
<div class="py-3">
    <div class="table-responsive">
    <table class="table table-striped  table-hover text-center table-responsive-sm" id="party_table">
        <thead class="table-head">
            <tr>

                <th scope="col">Party Name</th>
                <th scope="col">Email</th>
                <th scope="col">Contact</th>
                <th scope="col">Country</th>
                <th scope="col">Sub Country</th>
            </tr>
        </thead>
        <tbody>
            {% for party in parties %}
            <tr>

                <td class="pt-3">{{party.party_name}}</td>
                <td class="pt-3">{{party.email}}</td>
                <td class="pt-3">{{party.contact}}</td>
                <td class="pt-3">{{party.country}}</td>
                <td class="pt-3">{{party.sub_country.country_category}}</td>
            </tr>
            {% endfor %}

        </tbody>
    </table>

</div>
</div>
</div>

{% endblock %}

{% block js %}

    <!-- Datatable -->

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.css">
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.js"></script>
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>



<script>

    $(document).ready(function() {
        $('#party_table').DataTable( {
            dom: 'Bfrtip',
            "pageLength": 50,
            buttons: [
                {
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
                {
                    extend: 'pdfHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
            ]
        } );
    } );
</script>


{% endblock %}

how can i load data through server side processing? I am confused how to implement using help in docs. Please provide full implememtation from scratch.

Is there any alternative can also suggest?

Answer 1

When dealing with large datasets with Datatables you want to avoid using the iteration at the TR level and instead you want to set the data attribute within the DataTable definition.

https://mail.datatables.net/forums/discussion/32107/how-to-load-an-array-of-json-objects-to-datatables

You can do this by creating a JSON object within the script tag and then iterating parties and building up the JSON.

Datatables renders faster when the source of the data is defined in this way.

Another way would be to use server side AJAX (https://datatables.net/examples/data_sources/ajax) most examples you'll find for this will use PHP though.

For your code it would be something like this:

<script>
let data = [
{% for party in parties %}
    {
      "party_name": "{{ party.party_name }}",
      "email": "{{ party.email }}",
      "contact": "{{ party.contact }}",
      "country": "{{ party.country }}",
      "country_category": "{{ party.sub_country.country_category }}"
    }{% if not forloop.last %},{% endif %}
{% endfor %}
];

    $(document).ready(function() {
        $('#party_table').DataTable( {
            dom: 'Bfrtip',
            "pageLength": 50,
            buttons: [
                {
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
                {
                    extend: 'pdfHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
            ],
            data: data,
            columns: [
                { data: 'party_name'},
                { data: 'email'},
                { data: 'contact'},
                { data: 'country'},
                { data: 'country_category'}
            ]
        } );
    } );
</script>

So the in the first part you define the JSON structure then you pass that into the datatables configuration against the data parameter, then you use the columns parameter to tell it the order in which to use the data

To help with debugging and seeing which records from your DB are picked up use this instead which will display the PK of the record in the source so you can cross check against the DB

<script>
let data = [
{% for party in parties %}
    {
      /* PK={{ party.pk }} */
      "party_name": "{{ party.party_name }}",
      "email": "{{ party.email }}",
      "contact": "{{ party.contact }}",
      "country": "{{ party.country }}",
      "country_category": "{{ party.sub_country.country_category }}"
    }{% if not forloop.last %},{% endif %}
{% endfor %}
];

    $(document).ready(function() {
        $('#party_table').DataTable( {
            dom: 'Bfrtip',
            "pageLength": 50,
            buttons: [
                {
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
                {
                    extend: 'pdfHtml5',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3, 4 ]
                    }
                },
            ],
            data: data,
            columns: [
                { data: 'party_name'},
                { data: 'email'},
                { data: 'contact'},
                { data: 'country'},
                { data: 'country_category'}
            ]
        } );
    } );
</script>