too SLOW issue

too SLOW issue

garyhsugaryhsu Posts: 4Questions: 1Answers: 0

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?

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • garyhsugaryhsu Posts: 4Questions: 1Answers: 0

    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?

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

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

    Allan

  • garyhsugaryhsu Posts: 4Questions: 1Answers: 0

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

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

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

    Allan

  • garyhsugaryhsu Posts: 4Questions: 1Answers: 0

    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"}

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    edited January 2018

    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

  • rianghoriangho Posts: 1Questions: 0Answers: 0
    edited November 2019

    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>
    

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

This discussion has been closed.