Loading time of the datatable is very slow

leningi

Sorry, my english is bad.

I'm using Xampp and the codeigniter framework.

I have a table with more than three hundred thousand rows and using Server-side processing, however, it takes three minutes to process the table, this is normal?

my JavaScript is:

$(document).ready(function() {
      
     //datatables
     $('#tbltecate').DataTable({
         "lengthMenu": [ 5, 10, 25, 50, 75, 100 ],
         "paging": true,
         "deferRender": true,
         "responsive": true,
         "order": [],
         "language": {
          "url": "http://cdn.datatables.net/plug-ins/1.10.16/i18n/Spanish.json"
         },
         "processing": true, 
         "serverSide": true,
  
         
         "ajax": {
             "url": urlgetdata,
             "type": "POST",
             "data": function ( data ) {
                data.seccion = $('#seccion').val();
                data.nombre = $('#nombre').val();
                data.domicilio = $('#domicilio').val();
             }
         },
         "columnDefs": [
         { 
             "targets": [ 0 ],
             "orderable": false,
         },
         ],
  
     });
     $('#btn-filter').click(function() { 
        table.ajax.reload();
    });

    $('#btn-reset').click(function(){
        $('#form-filter')[0].reset();
        table.ajax.reload();
    });
 });

 function reload_table()
{
    table.ajax.reload(null,false);
}

 function update(id, estatus) {
     $.ajax({
         url : urlUpdateProm + id + "/" + estatus,
         type : "POST",
         data : $('#form_prom').serialize(),
         dataType : "JSON",
         success: function(data) {
             if (data.status) {
                reload_table();
             } else {
                alert('Error adding / update data');
             }
         },
         error: function (jqXHR, textStatus, errorThrown) {
            alert('Error adding / update data');
         }
     });
 }

Beforehand thank you very much.

allan

No - that isn't normal. Have you fully implemented server-side processing?

Is the server only returning 10 records at a time?

Allan

leningi

I dont know exactly. I am learning to use datatables

Json return:
recordsFiltered : 324000
recordsTotal : 324000

My controller and model are:

<?php
public function getPoblacionTecate()
    {
        $list = $this->poblacion->getTecate();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $poblacion) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = $poblacion->nombre;
            $row[] = $poblacion->apellido_paterno;
            $row[] = $poblacion->apellido_materno;
            $row[] = $poblacion->calle;
            $row[] = $poblacion->numero_exterior;
            $row[] = $poblacion->numero_interior;
            $row[] = $poblacion->colonia;
            $row[] = $poblacion->clave;
            $row[] = $poblacion->seccion;

            $row[] = $poblacion->id_persona;
 
            $data[] = $row;
        }
 
        $output = array(
                        "draw" => $_POST['draw'],
                        "recordsTotal" => $this->poblacion->count_all(),
                        "recordsFiltered" => $this->poblacion->count_filtered(),
                        "data" => $data,
                );
        
        echo json_encode($output);
    }

<?php
public function _get_datatables_query() {

     if($this->input->post('seccion'))
     {
         $this->db->where('seccion', $this->input->post('seccion'));
     }
     if($this->input->post('nombre'))
     {
         $this->db->like('nombre', $this->input->post('nombre'));
     }
     if($this->input->post('calle'))
     {
         $this->db->like('calle', $this->input->post('calle'));
     }
     if($this->input->post('clave'))
     {
         $this->db->like('clave', $this->input->post('clave'));
     }


    $this->db->select('p.id_persona, p.nombre, p.apellido_paterno, p.apellido_materno, p.calle, p.numero_exterior, p.numero_interior, p.colonia, p.clave, s.seccion');
    $this->db->from($this->table);
    $this->db->join('datos_distrito_ocho_seccion s', 'p.id_seccion = s.id_seccion');
    
    $i = 0;
    
    foreach ($this->column_search as $item) { // loop column 
      
      if($_POST['search']['value']) {
        
        if($i===0) { 
          $this->db->group_start(); 
          $this->db->like($item, $_POST['search']['value']);
        } else {
          $this->db->or_like($item, $_POST['search']['value']);
        }
        if(count($this->column_search) - 1 == $i) { 
          $this->db->group_end(); 
        }
      }
      $i++;
    }
    
    if(isset($_POST['order'])) { 
      $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } else {
      if(isset($this->order)) {
        $order = $this->order;
        $this->db->order_by(key($order), $order[key($order)]);
      }
    }
        
        if(isset($_POST['order'])) { 
      $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } 
        else {
      if(isset($this->order)) {
        $order = $this->order;
        $this->db->order_by(key($order), $order[key($order)]);
      }
        }
  }

  public function getTecate() {
    $this->_get_datatables_query();
    
    if($_POST['length'] != -1) {
      $this->db->limit($_POST['length'], $_POST['start']);
      $query = $this->db->get();
      return $query->result();
    }
  }

  public function count_filtered() {
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
  }
  
  public function count_all() {
    $this->db->from($this->table);
        return $this->db->count_all_results();
  }

allan

I see the limit there, so yes it looks like it probably is. Are there only ten items in the JSON data array? If you link to the page I can check.

Allan

leningi

The problem is that, I'm on localhost.

But I attached an image.

(I reduced the database to 150000 rows)

allan

It appears that the time taken is in the server-side script. What database library are you using? Is it pulling in everything from the SQL server? Are you able to get it to output the SQL that it is using?

Allan

leningi

I use XAMPP (MariaDB, phpMyAdmin)

<?php

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'datos',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_spanish2_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

leningi

mysqli_driver.php

allan

That doesn't appear to have worked (the attachment), could you try it with a .txt extension please?

I suspect you'll need to enable PHP tracing (perhaps something like this) to determine why your PHP script is taking so long to execute.

Allan

leningi

mysqli_driver.php (txt)

allan

Thanks. Yes, I think you would almost certainly need to use a tracer or profiler in your PHP. I don't immediately see what is wrong with the code there.

Allan

leningi

Thank you very much! Allan. I already found the problem.

The problem had to do with the JOIN statement, add LEFT was solved.
In SQL it would be "LEFT JOIN table2 ON table1.column_name = table2.column_name;"

AndyStewart

I have several screens with large datatables (up to 10,000 rows). I found the following worked for me (load time for 10,000 rows approx 5 seconds in Chrome)

CSS definition of:

    .webgrid-table-hidden
    {
        display: none;
    }

In your WebGrid definition, include the CSS from above:

@GridView.GetHtml(
tableStyle: "table table-striped table-bordered table-responsive table-hover cell-border compact webgrid-table-hidden"...

This hides the table on initial load.

Now in the document.ready jquery, add this:

        var GridView = $('#GridView').DataTable({});

        $('#GridView').show();
        GridView.columns.adjust().draw();

I found this significantly improves loading times...