too SLOW issue

garyhsu

I got a 10M entries table. Even with Server-side processing. It takes more than 30 seconds to show the Next page (10 entries). Any ideal why? any solutions?

allan

Happy to take a look at a test case showing the issue. Have you fully implemented server-side processing and the server is only returning 10 rows?

Allan

garyhsu

Thank you for your reply.^^ Actually, I doubt that SSP is fully working in my setting. I generated a download package from "Editor->Generator". Then I add "serverSide: true," in javascript. The result shows correct but slow. 10 rows in a page and it shows over 1M pages at the bottom of the table. Did I miss something?

allan

Did you modify the ajax option in the DataTable to send the request as POST? See this example.

Allan

garyhsu

Yes, as the example. I use ajax like following setting
$('#mytable').DataTable( {
dom: 'Bfrtip',
ajax: {
url: "php/mytable.php",
type: "POST"
},
serverSide: true,
columns: [ ..............

allan

That should be all that is needed. What is the JSON that the server-side is sending back?

Allan

garyhsu

The JSON which server-side send back as following, Looks good, it just sent back 10 records. but it takes a long time. Also, my server's CPU shows high loading of mysqld while waiting for server-side sending back. it doesn't make sense that to query 10 records will get that kind of high loading.

{"data":[
{"DT_RowId":"row_11068703","id":"11068703”,”…..
{"DT_RowId":"row_11068704","id":"11068704”,”…..
…..
…
……
{"DT_RowId":"row_11068711","id":"11068711”,”…..
{"DT_RowId":"row_11068712","id":"11068712”,”…..
"options":[],
"files":[],
"draw":3,
"recordsTotal":"11060640",
"recordsFiltered":"11060640"}

allan

What I would suggest in that case, is either:

1. Enable slow query logging on your SQL server. That will output the query being used to a log file, or
2. Modify the server-side processing script a little to log the SQL query it is generating.

Once you have that query you can add EXPLAIN before the SELECT ... part and run it against the database directly. That will show detailed information of what is taking the time when executing the query. It might just be that you need to add an index or two (e.g. to speed up sorting).

Allan

riangho

Hi, I kindda have problem, my data table is too slow

<script>
    $('#table-member').DataTable({
        ajax: 'member_data',
        columns: [
            { data: 'name', name: 'name' },
            { data: 'email', name: 'email' },
            { data: 'phone', name: 'phone' },
            { data: 'detail', name: 'detail'},
        ],
        language: {
        searchPlaceholder: 'Search...',
        sSearch: '',
        lengthMenu: '_MENU_ items/page',
        destroy: true
        },
        dom: 'Bfrtip',  
     buttons: [  
       'copy', 'csv', 'excel', 'pdf', 'print'  
     ],
     "bProcessing": true,
    "bServerSide": true, 
    }).fnDestroy();
</script>

colin

Hi @riangho ,

What steps have you taken to determine where the slowness is? Is it on the server when it responds to the ajax request? Or is it on the client?

Either way, this section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin