Datatables take too long to load

besimm

Hello,
I found datatables by trying different templates so I implemented it on my website. The way I implemented it is not the most logical way (more copying less writing) and everything worked. As my SQL tables began growing datatables started taking more time to load. Now, a table with 730 rows takes around 8 seconds to load (and when using search the datatable freezes for like 2 seconds). The way the datatable works is like this: I use mysqli to get rows from the database and put them in a php while loop. After it gets the rows from the database datatables come into action and sort the results, but the time it takes to do that is around 8 seconds. Is there a way to make it faster because my tables are going to grow more and more (10000+ rows).

Here are my codes:

<?php > $result = mysqli_query($db, "SELECT * FROM table_sales") or die(mysql_error()); > ?>
<tbody>
<?php while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){?>
<tr>
<td><?php echo $row['something']; ?></td>
<td><?php echo $row['anotherthing']; ?></td>
<td><?php echo $row['something2']; ?></td>
</tr>
<?php } ?>
</tbody>

$('#datatables').dataTable({
"pageLength": -1,
"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
scrollY: "500px",
scrollX: true,
scrollCollapse: true,
// fixedColumns:{
// leftColumns: 2
// },
"oLanguage": {
"sSearch": "<b class='fa fa-search fa-lg'> </b>",
"sLengthMenu": "<b id='data-menu'><b class='fa fa-eye fa-lg'> </b>Show MENU records</b> "
},
"pagingType": "full_numbers",
dom: 'lBfrtip',
"order": [[ 0, 'desc' ]],
});

How can I optimize my datatable?

Thanks

bindrid

1. Make sure your database is optimized, etc.
2. Add sort order to your sql. You are spending time writing out all that html strings only to have DataTables resort it.
3. See if deferRender makes a difference https://datatables.net/reference/option/deferRender
4. Also, might consider using AJAX to get your data instead of creating the html table that DataTables is just going to rewrite.