optimize jquery datatable with a database with more than 30,000 records

Question

What is the best way to optimize database data return? the ideal would be to store (on the server-side) all data (select * from data) in an array of objects and send it to the client-side? from there I do the processing of this array via javascript and I populate in my table?

do something like this? follow the jsfiddle link below http://jsfiddle.net/V2Kdz/

some tips?

Answer 1

Uploading all data at once is a bad idea, regardless of whether you save it on the front end or back end, always limit your results, in the case of "database" the best solution is to use LIMIT (I believe that in SQL Server we use OFFSET instead of LIMIT), so you should only send the requested page data to the front-end.

I don’t know which bank you’re using, but the syntax is very similar in most banks, it works like this:

SELECT ... LIMIT [offset,] row_count

The offset is the position in the bank, the row_count is the result limit that you will display, note that if you use it like this:

SELECT ... LIMIT 10

He will do the offset be equal to 1 because he was omitted and 10 will be the row_count.

Usually for each pagination we use a limit of 15 to 30 (this varies according to each one).

In your code there’s a <select> in the option sLengthMenu of $.dataTable, it should send the selected value to the back-end, in case it selected to show 10, it should send a request to the server and should run the query like this (http://localhost/page.php?limit=10):

Note: I don’t know if you are using mysql or other type of database, but the logic is the same

$offset = 1;
$row_count = $_GET['limit'];

$stmt = $mysqli->prepare('SELECT ... LIMIT ?, ?');

$stmt->bind_param('i', $offset);
$stmt->bind_param('i', $row_count);

//query: SELECT ... LIMIT 1, 10;

If you want to go to page 2, you must multiply the $offset, by the amount of lines that will display (http://localhost/page.php/?page=2&limit=10):

$offset = 1;

if (isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] > 1) {
    $offset = ($_GET['page'] - 1) * $_GET['limit'];
}

$row_count = $_GET['limit'];

$stmt = $mysqli->prepare('SELECT ... LIMIT ?, ?');

$stmt->bind_param('i', $offset);
$stmt->bind_param('i', $row_count);

//query: SELECT ... LIMIT 10, 10;

• Page 3 (http://localhost/page.php.php?page=3&limit=10) will generate:

  SELECT ... LIMIT 20, 10;
  

• Page 4 (http://localhost/page.php.php?page=4&limit=10) will generate:

  SELECT ... LIMIT 30, 10;
  

• Page 5 (http://localhost/page.php.php?page=5&limit=10) will generate:

  SELECT ... LIMIT 40, 10;
  

To use with WHERE, do something like:

SELECT * FROM tabela WHERE foo='abc' OR foo='xyz' LIMIT 1, 10

And so on and so forth.

Documentation

• Mysql: https://dev.mysql.com/doc/refman/5.0/en/select.html
• Sql Server 2008+: https://msdn.microsoft.com/pt-br/library/ms186734.aspx
• Sql Server 2012 (Compact): https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx
• Postgresql: http://www.postgresql.org/docs/8.1/static/queries-limit.html
• Oracle: http://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

Answer 2

In the option data ajax, pass a function:

"data": function ( d ) {
    d.suavariavel = "seu valor";
}

So you can, through variables, make a select limiting the number of records returned. Follows an example that I use and works perfectly:

var dtini  = $('#dtini').val();
var dtfim  = $('#dtfim').val();
var temp = $('#dtini').val().split("/");
var d1     = new Date(temp[2], temp[1]-1,temp[0]);
var temp = $('#dtfim').val().split("/");
var d2     = new Date(temp[2], temp[1]-1,temp[0]);
if (d2 < d1){
    errorAlert("Data final não pode ser maior que data inicial");
}else{
    var totcli = $('#totcli').dataTable({
        "bDeferRender"   : true,
        "bProcessing"    : true,
        "aaSorting"      : [],
        "sPaginationType": "full_numbers",
        "ajax"           : {
            "url" : "../php/rotinas.php",
            "type": "POST",
            "data": function (a) {
                a.vendaspr = 0;
                a.dtini    = dtini;
                a.dtfim    = dtfim;             
            }   
         }
    }); 
}

In this example, return the records that date between the initial and final date.

Answer 3

Use the filter limit SQL and client-side paging technique, no need to send a flood of data at once.