Loading time of the datatable is very slow

Loading time of the datatable is very slow

leningileningi Posts: 7Questions: 1Answers: 1

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 62,006Questions: 1Answers: 10,165 Site admin

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

    Is the server only returning 10 records at a time?

    Allan

  • leningileningi Posts: 7Questions: 1Answers: 1
    edited April 2018

    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();
      }
    
  • allanallan Posts: 62,006Questions: 1Answers: 10,165 Site admin

    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

  • leningileningi Posts: 7Questions: 1Answers: 1
    edited April 2018

    The problem is that, I'm on localhost.

    But I attached an image.

    (I reduced the database to 150000 rows)

  • allanallan Posts: 62,006Questions: 1Answers: 10,165 Site admin

    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

  • leningileningi Posts: 7Questions: 1Answers: 1

    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
    );
    
  • leningileningi Posts: 7Questions: 1Answers: 1

    mysqli_driver.php

  • allanallan Posts: 62,006Questions: 1Answers: 10,165 Site admin

    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

  • leningileningi Posts: 7Questions: 1Answers: 1

    mysqli_driver.php (txt)

  • allanallan Posts: 62,006Questions: 1Answers: 10,165 Site admin

    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

  • leningileningi Posts: 7Questions: 1Answers: 1
    Answer ✓

    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;"

  • AndyStewartAndyStewart Posts: 2Questions: 0Answers: 0

    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...

This discussion has been closed.