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?

Take a look at the following notes here which attempt to provide a server-side overview, and to clarify the official documentation. Pay specific attention to the JSON request and response data structures used with server-side processing (linked to in the answer) - and remember that your Django code will be responsible for all sorting, filtering and paging logic. This assumes a pure ajax approach - but there are other approaches, also, of course. — andrewJames
– andrewJames, Commented Aug 20, 2021 at 13:30
Just to add: "Please provide full implementation from scratch." - Someone may do all that coding for you, if you are lucky. But that is not generally how Stack Overflow works. Try to ask a specific question about a specific problem you may be facing. — andrewJames
– andrewJames, Commented Aug 20, 2021 at 13:33

Steve Mapes · Accepted Answer · 2021-08-20 10:47:19Z

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>

edited Aug 20, 2021 at 10:47

answered Aug 20, 2021 at 9:48

Steve Mapes

9418 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Pulkit Arora Over a year ago

It Duplicate all rows and also shows warning and data not shown properly. I will attach images through link.

Steve Mapes Over a year ago

and you got rid of the iterator within the HTML portion right so your tbody tag should be empty. So just <tbody></tbody>

Pulkit Arora Over a year ago

It Duplicate all rows and also shows warning and data not shown properly. I will attach images through link. link

Steve Mapes Over a year ago

I corrected the example, my clipboard had the wrong content so I put in master_bill rather than. For the duplicates if you look at the rendered source are do the entries appear in the JSON object twice? If so check your database for duplicate records and add a JS comment with the PK. EG. add /* {{ party.pk }}*/ into the loop so you can see what records it's picking up

Steve Mapes Over a year ago

I've updated the answer with a full example of adding in the PK for debugging to make that clearer

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related