Server-side Processing Load Data Very Slow

taufik-kurahman

Hi,
I am using server-side DataTables for works with 2000++ rows. but, loading data when page is load, change order column, go to other table page and doing searching is too slow.

DataTables version i am using is 1.10.19 with bootstrap 3 style and CodeIgniter 3.

HTML :

<table id="site-table" class="table table-striped table-bordered" style="width:100%">
  <thead>
    <tr>
      <th style="width:5%">No</th>
      <th>SITE ID</th>
      <th>WID</th>
      <th>SITE NAME</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>

JS :

var site = $('#site-table').DataTable({
    "destroy": true,
    "processing":true,
    "serverSide":true,
    "order":[],
    "ajax":{
        url:"<?php echo base_url().'Atp/getSiteList'; ?>",
        type:"POST"
    },
    "columnDefs":[
        {
          "targets":[0],
          "orderable":false,
        },
    ],
    "aoColumns":[
      null,
      null,
      null,
      null
    ],
  });

Controller :

public function getSiteList()
{
  $fetch_data = $this->M_ATP->getSiteList();
  $data = array();
  $no = 1;
  foreach($fetch_data as $row)
  {
    $sub_array = array();
    $sub_array[] = $no++;
    $sub_array[] = '
      <a href="'.base_url().'site_detail/'.$row->site_id_actual.'" target="_blank">'.$row->site_id_actual.'</a>
    ';
    $sub_array[] = $row->wid;
    $sub_array[] = $row->site_name;
    $data[] = $sub_array;
  }
  $output = array(
    "draw"                =>     intval($_POST["draw"]),
    "recordsTotal"        =>     $this->M_ATP->getSiteListNumRows(),
    "recordsFiltered"     =>     $this->M_ATP->getSiteListFilteredData(),
    "data"                =>     $data
  );
  echo json_encode($output);
}

Model :

public function getSiteListQuery()
{
  $this->db->select(array("tb_aktual.*", "tb_site_list.*"));
  $this->db->from("tb_aktual");
  $this->db->join("tb_site_list", "tb_site_list.site_id_po = tb_aktual.site_id_actual");
}

public function getSiteList()
{
  $this->getSiteListQuery();
  $order = array(null, "tb_aktual.wid", "tb_aktual.site_id_actual", "tb_site_list.site_name");
  if(isset($_POST["order"])){
    $this->db->order_by($order[$_POST["order"]["0"]["column"]], $_POST["order"]["0"]["dir"]);
  }
  else {
    $this->db->order_by("tb_aktual.site_id_actual", "ASC");
  }
  if($_POST["length"] != -1){
    $this->db->limit($_POST['length'], $_POST['start']);
  }
  if(isset($_POST["search"]["value"]))
  {
    $this->db->where("
      tb_aktual.wid LIKE '".$_POST["search"]["value"]."%' OR
      tb_aktual.site_id_actual LIKE '".$_POST["search"]["value"]."%' OR
      tb_site_list.site_name LIKE '".$_POST["search"]["value"]."%'
    ");
  }
  $query = $this->db->get();
  return $query->result();
}

public function getSiteListFilteredData()
{
  $this->getSiteListQuery();
  if(isset($_POST["search"]["value"]))
  {
    $this->db->where("
      tb_aktual.wid LIKE '".$_POST["search"]["value"]."%' OR
      tb_aktual.site_id_actual LIKE '".$_POST["search"]["value"]."%' OR
      tb_site_list.site_name LIKE '".$_POST["search"]["value"]."%'
    ");
  }
  $query = $this->db->get();
  return $query->num_rows();
}

public function getSiteListNumRows()
{
  $this->getSiteListQuery();
  $query = $this->db->get();
  return $query->num_rows();
}

Any ideas?
Thanks

colin

Hi @taufik-kurahman ,

You need to profile it, to see where the delay is - it could be on the client's table draw, on the network, on the database reading, or on the server-side script. Best bet is to eliminate one at a time until you find out where the performance bottleneck is. This FAQ page may also help,

Cheers,

Colin

taufik-kurahman

Hi @colin ,

Thanks, it says to activate paging which of course I have done. I have also tried deferRender but still not working.

taufik-kurahman

Hi again,
i have solved this by my self, this project still build in my local machine which is AMD A6 CPU and 8 GB RAM. Since server-side is loading data in server i think it's depend on hardware too. So i test on my friend laptop with Core i5 and 4 GB RAM and it works fine (fast loading). I tested again in my laptop with deleted rows until < 1000 and it's faster than before.

Thanks.