datatable optimization slow loading time

drakas

Link to test case: sorry, it is internal portal with sensitive data, so I can not post link to test the case

Debugger code (debug.datatables.net):

Table:

Information about 1 table available

log

Data source: DOM
Processing mode: Client-side
Draws: 8
Columns: 3
Rows - total: 24806
Rows - after search: 209
Display start: 150
Display length: 50

Versions:

LibraryInfoInstalledLatest
DataTables available1.10.20
AutoFill-2.3.7
Buttons1.6.1
ColReorder-1.5.5
Editor-2.0.5
FixedColumns-4.0.1
FixedHeader-3.2.0
KeyTable-2.6.4
Responsive-2.2.9
RowGroup-1.1.4
RowReorder-1.2.8
Scroller-2.0.5
SearchBuilder-1.3.0
SearchPanes-1.4.0
Select-1.3.3

Check for common issues:
15 tests complete. No failures or warnings found!

Error messages shown: no errors

Description of problem: page loads dom realy slow, main issue is rendering time, how can I optimize this? Maybe can someone show me example how to fix loading time with big datatables?

322 ms Loading
3935 ms Scripting
4849 ms Rendering
472 ms System
3678 ms Idle
13256 ms Total

colin

This section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin

drakas

Hello collin, corect me if I wrong, I should use Client-side processing - Ajax sourced data, this means i add ajax url, where table should send pagination data there should be loaded only selected page data and it must be formated as and object and returned to frontend?

colin

With 24k rows, you should be OK with client-side processing provided you're not doing too much processing/rendering on those rows. With client-side, all data is sent to the browser, and the browser works out the paging, filtering and ordering. If you want only the selected page data to be sent, then that's exactly what serverSide does - as this greatly limits the data sent.

There are examples for both which would probably help understand what's happening - check out the network tab on the developer tools. This is client-side, and for server-side,

Colin

drakas

Well I think I'm doing to much processing, becuase it loads very slow, could some one help me to turn this code to server side sript?

$select_log = "SELECT * FROM log ORDER BY timestamp DESC";

        $query_log = mysqli_query($conn, $select_log);

        $num_log = mysqli_num_rows($query_log);



        for($k=0;$k<$num_log;$k++) {

            $temp_log = mysqli_fetch_array($query_log);

            $log_id = $temp_log['id'];

                $log_user_id       = $temp_log['user_id'];

                $log_action        = $temp_log['log_action'];

                $log_client_id     = $temp_log['client_id'];

                $log_call_id       = $temp_log['call_id'];

                $log_client_status = $temp_log['client_status'];

                $log_timestamp     = $temp_log['timestamp'];



                $log_user_name    = mysqli_fetch_array(mysqli_query($conn,"SELECT name FROM user_login WHERE id = '$log_user_id'"))['name'];

                $log_user_surname = mysqli_fetch_array(mysqli_query($conn,"SELECT surname FROM user_login WHERE id = '$log_user_id'"))['surname'];

                $log_action_text  = mysqli_fetch_array(mysqli_query($conn,"SELECT text FROM log_action WHERE id = '$log_action'"))['text'];

                if($log_client_id != NULL) {

                $log_client_name = mysqli_fetch_array(mysqli_query($conn,"SELECT cl_name FROM clients_main WHERE id = '$log_client_id'"))['cl_name'];

                }

                if($log_call_id != NULL) {

                    $log_call_con_id   = mysqli_fetch_array(mysqli_query($conn,"SELECT cl_con_id FROM calls WHERE id = '$log_call_id'"))['cl_con_id'];

                    $log_call_con_name = mysqli_fetch_array(mysqli_query($conn,"SELECT cl_contact_name FROM clients_contact_person WHERE id = '$log_call_con_id'"))['cl_contact_name'];

                }

                if($log_client_status != NULL) {

                    $log_client_status_text = mysqli_fetch_array(mysqli_query($conn,"SELECT status FROM clients_status WHERE status_id = '$log_client_status'"))['status'];

                    $log_client_status_color = mysqli_fetch_array(mysqli_query($conn,"SELECT bg_color FROM clients_status WHERE status_id = '$log_client_status'"))['bg_color'];

                    $log_client_status_color = substr($log_client_status_color, 3);

                }

allan

You are doing 8 queries for every row? Wow - yup, that will slow things down a fair bit. You really want to combine that into a single query with joins.

$num_log

That seems to me that you are running it on all 24k rows rather than just the 10 (or whatever) DataTables uses for the current page. See this manual page for the parameters DataTables sends and you need to use for processing the request.

Allan

drakas

hello Allan, if I somehow transform it with JOINS, maybe this would be enough not to switch to server side with selected count for that page records and it would be all good with dom client side all the rows with JOIN mysql query, what do you think?

allan

Possibly. With 24k rows, depending what is in the rows (i.e. the size of each) then the download of the data for all of them might be fast (or might be slow!). You are into the grey area there where there isn't a clear yes or no to server-side processing.

I'd suggest trying to may your SQL a single statement first - that will most certainly improve performance, regardless of which why you go, and then you can decide based on the resulting performance if you want to use server-side processing or not.

Allan

drakas

Well I tried to optimize queries but this does'nt help a lot, because the main issue is 21144 ms Rendering.

kthorngren

the main issue is 21144 ms Rendering.

Have isolated the time to see how much is Datatables loading? How long does your page take without Datatables?

Kevin

drakas

Hello Kevin,

How to do this?

kthorngren

First I would temporarily remove the Datatables init code to see how long the page takes to load without Datatables. This should help to determine if Datatables rendering is the problem.

Kevin