Processing Load very slow takes more than 30 seconds

sylarlocke

Hello, I have been trying to load the content of a single query for days, I have tried both from the server side and directly and both do not work take the same.

I have incorporated the "scroller" plugin but it takes the same, someone can help me, I leave the code here, use php and codeigniter 3.

JS

var table = $('#datatabletransaction').DataTable({
    "deferRender":    true,
    "scrollY":        500,
    "scroller":       true,
    "responsive": true,
    "processing": true,
    "fixedHeader": true,
    "serverSide":true,
        "lengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "Todos"]],
        "ajax": {
            "url": mostrarcontenido,
            "type": "POST"
    },        
    language": {
          "url": "http://cdn.datatables.net/plug-ins/1.10.16/i18n/Spanish.json"
    },
    "fixedColumns": true,
    "iDisplayLength": 5,
    "order": [[ 2, "desc" ]]
    });

Controller

public function mostrartablatransaccionestelefonos(){
      $this->load->model('tablas_model');
      $data = array();
      $list = $this->tablas_model->mostrar_registros_ventacompra($estado);
      foreach ($list as $person) {
          $row = array();
          $row[] = $person->num_factura;
          $row[] = $person->num_albaran;
          $row[] = $person->transacion;
          $row[] = $person->imei;
          $row[] = $person->nombre;
          $row[] = $person->fecha_registro;
          $row[] = $person->modelo;
          $row[] = $person->marca;
          $row[] = $person->color;
          $row[] = $person->memoria;
          $row[] = $person->precio_iva;
          $row[] = $person->precio_sin_iva;
    
          $output = array(
                  "data" => $data,
          );
          echo json_encode($output);

   }

sylarlocke

I have executed the query in HeidiSql and this is the result
/ * Affected rows: 0 Rows found: 1.816 Warnings: 0 Duration for 1 consultation: 0.047 sec. * /

The query does not take half a second.
But how can I make it load data faster?

colin

You would need to profile the system end-to-end. Is the performance bottleneck getting the data off the server, or the rendering in DataTables? And if off the server, is it because the DB queries are slow, or is the creation of the JSON response?

If it's the client, this section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin

sylarlocke

Thanks for your help Colin, I explain the current operation.

1º The sql query takes less than 1 second (0.047 sec) to execute. Currently the query returns about 2000 (1,816) records, but as more records are added it can reach 10,000 perfectly. The table has 32 fields (although in the example I have put less).

2º Currently I do not use the server side ("serverSide": false), but I have used this option too and the result is the same, I have also used the plugin "scroller" ("dataTables.scroller.min.js") but the charging times remain the same.

3º What I am seeing is that when the json is generated it returns all the records at once so the response time is longer, this is the response of the json, since it returns 1,816 records and each record has 32 fields that are the ones that will be shown in each column:
XHR POST [HTTP / 1.1 200 OK 14403ms]

What do you recommend to be able to load fewer records or another way to minimize loading times?

colin

The best here would be to use serverSide with paging, that way only the records for that current page are requested/returned, heavily reducing that load.

The protocol is discussed here. Also see examples here.

Cheers,

Colin

sylarlocke

Thank you very much for your help, I've been restructuring the code all afternoon and I have started to see part of the one I don't know, I've managed to put pages, but every time I click on a page instead of changing the content is added to the current page and The first page is blank.

Any idea why this is happening?

colin

It's probably because you're not sending the data back in the expected format. I'd suggest looking at the examples I posted above, and comparing your responses with those in the examples. If still no joy, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Colin

sylarlocke

Hello again, thanks for the links I was looking at them and I think I have almost, I also followed several tutorials from other websites for very similar codeigniter, made a simple query to a table and the problem I am having is the same record Repeats all the page.

That is to say:
It returns 1816 records for 182 pages and each page of 10 repeated records in each file the same record, when the pulse on page 2 sells the next repeated record as well, the query is corrected and everything seems fine.

That could be happening?

colin

the problem I am having is the same record Repeats all the page

This sounds more of an issue with codeigniter than DataTables - DataTables wouldn't repeat those rows. I'm not familiar with codeigniter I'm afraid, maybe ask on their forum perhaps or StackOverflow.

Colin

sylarlocke

Thank you very much for your help collin, in the end I got it with a lot of patience, one of the main mistakes was in "limit" in "start" and "length" for some strange reason I was taking this inverted data, now it works perfectly.

The only thing that I would like to have is the possibility of putting a search in each particular and not general column, I will try to investigate.

Thank you very much for your help