I try to load 4 thousand records in a Datatable and it takes several seconds

Question

I try to load 4 thousand records in a Datatable and it takes several seconds

Navigation

#1 by (0 votes)

0

My problem is that when loading the BD records in the datatable it takes several seconds, eh I saw that you can only show the data you need to see and from there make a request for the other data but I do not understand how does that my code is as follows:

Datatable:

var table = $('#tableProveedores').DataTable({
                    bDeferRender: true,
                    responsive: true,
                    columnDefs: [{
                        // "targets": 7,
                        "orderable": false
                    }],
                    scrollX:        true,
                    data: response.provider,
                    columns: [
                        {data: "nombre"},
                        {
                            "render": function (data, type, row, meta){
                                var $select = $('<select class="form-control" id="tipo"><option id="basico" value="0">Basico</option><option value="1">Plata</option><option value="2">Oro</option><option value="3">Diamante</option></select>');
                                $select.find('option[value="'+row.tipo_cuenta+'"]').attr('selected', 'selected');
                                return $select[0].outerHTML;
                            }
                        },
                        {
                            "render": function (data, type, row, meta){
                                var $select = $('<select class="form-control" id="estado"><option value="0">Desactivado</option><option value="1">Pendiente</option><option value="2">Activo</option></select>');
                                $select.find('option[value="'+row.activo+'"]').attr('selected', 'selected');
                                return $select[0].outerHTML;
                            }
                        },
                        {data: "usuario"},
                        {data: "correo"},
                        {data: "contacto_celular"},
                        {"render":
                            function ( data, type, row ) {
                                return (row["localizacion_estado"] + ', ' + row["localizacion_pais"]);
                            }
                        },
                        {data: "fecha_creacion"},
                    ],
                    language: {
                        "url": "//cdn.datatables.net/plug-ins/1.10.16/i18n/Spanish.json"
                    }
                });

Controller:

public function extraerProveedores() {
    if($_POST) {
        $data = $this->proveedor->getAllAdmin();

        return $this->output->set_content_type("application/json")
                ->set_status_header(200)
                ->set_output(json_encode(['success' => true, 'provider' => $data]));
    }
}

Model:

public function getAllAdmin()
{
    $sql = 'SELECT proveedor.* ,usuario.correo, usuario.usuario, usuario.activo ,fecha_creacion FROM proveedor INNER JOIN usuario USING(id_usuario) ;';
    $query = $this->db->query($sql);

    return $query->result();
}

I searched already in the official documentation of Datatables but I can not understand how the data load can be optimized.

javascript codeigniter datatables

asked by Miguel Mota 30.11.2018 в 23:03

source

1 answer

Calculate Sum in real time with JS How do I get a list in the repository

score 0 · Answer 1

It is possible that a good part of the consumption of time (and resources in your pc) lies in the select that you are creating in the render. Think that if you are taking 4000 records, you are telling js to create 8000 selects, since you have 2 selects per record. This will create an important burden.

What you can do to optimize it would be (for example) the following:

In those fields, simply show the value you want (plain text: simply the value of "row.type_count").
Create a function that dynamically generates a select with the options you want (pass them by parameter and so it works for both selects).
Create an on.click event so that when you click on one of those two fields, you call the function "create_select" and insert the select in question in that cell.

Note: you will have to create other function functions of focusout / change / ... to re-save the selected value in your datatables and re-display only the text of the selected option.

Note2: another way is that instead of doing it with click and focusout / change / ... events, do it by adding one more column (s) to the datatables, for example: "edit". When you click on that cell (you put an icon, for example), do what I mentioned earlier: create two selects invoking the "create_select" function, insert them and at the same time replace your "edit" icon with a "save" icon . You create an event so that when you click on the save icon, only the texts of the selected options will be shown again.

This would reduce the number of selects from 8000 to 2