large jquery datatables are slow with django

Question

I am displaying a 20k row jquery datatable through django. It is very slow to render. So I am wondering what could be the alternative approaches to process it faster e.g., through defer rendering, to load only the parts that are displayed? The database is in sql.

I tried deferRender and serverSide options to true in datatable options but it is still very slow. I understand that those options work only when the data is called through AJAX. The examples given in datatables website are just dumps of AJAX data into predefined columns. In my case, I am using the data as argument to generate links and create icons etc.

Here is the code:

<div class="container col-9 pb-5 mb-5">
    <table id="drugstable" class="table table-hover" style="width: 100%">
            <thead>
              <tr>
                <th scope="col">#</th>
                <th scope="col">Drug</th>
                <th scope="col">Tool</th>
                <th scope="col">netZoo release</th>
                <th scope="col">Network</th>
              </tr>
            </thead>
            <tbody>
             {% for drug in drugs %}
                <tr>
                <th scope="row">{{ forloop.counter }}</th>
                <td><a href="{{ drug.drugLink }}">{{ drug.drug }}</a></td>
                <td>{{ drug.tool }}</td>
                <td>{{ drug.netzoo }} <a href="{{ drug.netzooLink }}">{{ drug.netzooRel }}</a></td>
                <td><a href="{{ drug.network }}" download><i class="fas fa-download"></i></a></td>
                </tr>
             {% endfor %}

            </tbody>
          </table>

Here is the corresponding JavaScript

<script>
$(function () {
  $('[data-toggle="popover"]').popover();
})

// Basic example
$(document).ready(function() {
    //datatables
    $('#drugstable').DataTable({
      "dom": "<'row'<'col-sm-12 col-md-6'l><'col-sm-12 col-md-6'f>>" +
             "<'row'<'col-sm-12'tr>>" +
             "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
    });
} );
</script>

Do a google search for grid controls. There is a lot to chose from. I use w2ui.com myself. IT has lots of options. 20k records is going to take a lot of time, even if you just had them in a prerendered html file. — Jason K
– Jason K, Commented Aug 29, 2019 at 21:19

FlipperPA · Accepted Answer · 2019-08-30 02:12:36Z

As mentioned in the comments, 20,000 rows is going to take a lot of time for the browser to render with jQuery DataTables. You may want to look into using jQuery DataTables with paginated loading. To do this, you need to provide your data from the Django backend using something like Django REST Framework.

The configuration is tricky to get right in jQuery. Suppose you have a Django REST Framework endpoint which provides three fields called col1, col2, and col3 from your Django model. Here's the JavaScript and HTML to load it progressively from an endpoint called https://example.com/api/my-endpoint/:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <!-- Include jQuery and DataTables -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.css">
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.js"></script>

    <title>My API in Data Tables</title>
</head>
<body>
    <table class="table table-hover" width="100%" id="mydatatable">
        <thead>
            <tr>
                <th>col1</th>
                <th>col2</th>
                <th>col3</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
<script>
    url = "https://example.com";
    endpoint = "/api/my-endpoint/";

    table = $('#mydatatable').DataTable({
        "processing": true,
        "serverSide": true,
        "lengthMenu": [10, 25, 50, 100],
        "pagingType": "simple",
        "info": true,
        "ajax": {
            "url": url + endpoint,
            "beforeSend": function (request) {
                request.setRequestHeader("Authorization", "Token MyTokenMyTokenMyTokenMyToken");
            },
            "data": function(d) {
                // Map DataTables field names to DRF field names
                // DataTables `start` becomes DRF `offset`
                // DataTables `length` becomes DRF `limit`
                //
                // Routines handle converting DataTables ordering and search
                // values for the DRF on input

                d.offset = d.start;
                d.limit = d.length;

                var ordering = '';
                $.each(d.order, function(k, v) {
                    if (v.dir == 'desc') {
                        ordering = '-';
                    }
                    if (d.columns[v.column].data == 'client_obj') { // catch ordering on client_obj column
                        d.columns[v.column].data = 'client__name'; // convert it to 'client__name' field expected by DFR endpoint
                    }
                    ordering += d.columns[v.column].data

                });
                d.ordering = ordering;
                d.search = d.search.value;
            },
            "dataFilter": function(data) {
                // Map DRF field names to Data Tables field names
                // DRF `count` becomes DataTables `recordsTotal`
                // DRF `count` becomes DataTables `recordsFiltered`
                // DRF `results` becomes DataTables `data`

                var json = jQuery.parseJSON(data);
                json.recordsTotal = json.count;
                json.recordsFiltered = json.count;
                json.data = json.results;

                return JSON.stringify(json); // return JSON string
            },
        },
        columns: [
            {data: 'col1'},
            {data: 'col2'},
            {data: 'col3'}
        ]
    });
</script>
</body>
</html>

This example also includes how to send a token for Token Authentication with DRF's included Token Auth, but if you're not requiring authentication, you don't need it. I hope this helps get you on the right track. Good luck!

Thanks. That put me on the way of finding the answer. I ended up using a third party plugin github.com/izimobil/django-rest-framework-datatables.

MyTwoCents · Accepted Answer · 2019-12-08 06:59:54Z

1

jquery Datatable has Pagination option where Number of records is controlled by server side logic.

You can activate Pagination by adding "serverSide": true something like this

$('#example').DataTable({
    "processing": true,
    "serverSide": true,
    "ajax": "../server_side/scripts/server_processing.php"
});

Datatable will add some parameter to the API call dynamically which can be used to write logic in serverside.

eg:

order:  asc
start:  20
length: 10

Refer this post for more detail and live demo with sample code

edited Dec 8, 2019 at 6:59

answered Aug 30, 2019 at 6:50

MyTwoCents

7,6723 gold badges28 silver badges56 bronze badges

Collectives™ on Stack Overflow

large jquery datatables are slow with django

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related