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

Loading all data at once is a bad idea, regardless of whether you leave them stored 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 data to the front-end.

I do not know which bank you're using, but the syntax is pretty similar on most banks, it works like this:

SELECT ... LIMIT [offset,] row_count

The offset is the position in the database, the row_count is the limit of results that you will display, note that if you use this way:

SELECT ... LIMIT 10

It will make offset equal 1 since it has been omitted and 10 will be row_count .

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

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

  

Note: I do not know if you are using mysql or another 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 should multiply the $offset by the number of rows it 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.

Documentation

• MySql: link
• Sql Server 2008 + : link
• Sql Server 2012 (compact): link
• PostgreSQL: link
• Oracle: link

In the% ajax% option, pass a function:

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

So you can, through the variables, do a select by limiting the number of records returned. Here is an example that I use and work 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, I return records that date from the start date to the end date.

Use the limit filter in SQL and the client-side paging technique, you do not have to send a flood of data at once.