Why does my table takes so much time to render (1000 documents+)

Noxcius

So i'm using this plug-in which is really good, but when i try to render some amount of documents ( 1000 + ) from mongo, it takes really a lot of time.
i'm having this project in class where i need to render a table with at least 5000 documetns, but it takes about 12 second+ for the table to render.
I would like to know if there is any way to make it render faster because it is very important to me.

wblakenc

I believe we are going to need a bit more information to assist. Things that would help:

1) Is the db hosted on a different computer than what is running the webpage?
2) When you say render 'documents', what do you mean?
3) Please post the JS you are using to render the table
4) Please post the sql you are using to retrieve the data

colin

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

Cheers,

Colin

Noxcius

@wblakenc
DB is hosted in heroku server, soon will be transferred to amazon server (very powerful server, the respond arrives very fast.
when i say "Documents" i mean items that are coming from the DB (array of objects)
the js i'm using is -

    function init_DataTables() {
        if (typeof($.fn.DataTable) === 'undefined') {
            return;
        }
        var handleDataTableButtons = function() {
            if ($("#datatable-products").length) {
                var activeFileName = 'Diamonds-List';
                table = $("#datatable-products").DataTable({
                        dom: "Bfrtip",
                        deferRender: true,
                        retrieve: true,
                        'order': [
                        ],
                        'columnDefs': [{
                            orderable: false,
                            targets: [0]
                        }],
                        "iDisplayLength": 30,
                        "orderClasses": false,
                        buttons: [{
                                extend: 'excelHtml5',
                                className: 'exportExcelButton',
                                title: activeFileName
                            },
                            {
                                extend: 'excelHtml5',
                                text: 'Export selected',
                                className: 'exportSelectedButton',
                                exportOptions: {
                                    columns: ':visible:not(.not-exported)',
                                    modifier: {
                                        selected: true
                                    }
                                },
                                title: activeFileName
                            },
                            {
                                extend: 'pdfHtml5',
                                title: activeFileName,
                                text: 'PDF',
                                orientation: 'landscape'
                            },
                            {
                                extend: 'csv',
                                text: 'Export to CSV',
                                className: 'export-csv',
                                exportOptions: {
                                  columns: "th:not(.not-export)"
                                },
                                title: 'Diamonds'
                                // title: activeFileName
                            },
                            'print', 'copy'
                        ],
                 
             "language": {
          "search": "",
          "searchPlaceholder": "Type name, company etc."
          },
                        select: {
                            style: "multi"
                        },
                        "fnDrawCallback": function(oSettings) {
                            // Your function(s);
                        }

                        // responsive: true
                    }).on('search.dt', function() {
                        var snapshot = table
                            .rows({
                                search: 'applied',
                                order: 'index'
                            })
                            .data()
                            .toArray()
                            .toString();
                        var currentSnapshot = table.settings().init().snapshot;
                  activeDashboardManager.showProductsView2BySearchSort($('#datatable-products').DataTable()
                            .rows({
                                filter: 'applied'
                            }).data());
                    })
                    .on('datachange.dt', function() {
                        //alert('data has changed')
                        //updateGraph( GraphData )
                    })

            }
        };
        TableManageButtons = function() {
            "use strict";
            return {
                init: function() {
                    handleDataTableButtons();
                }
            };
        }();

        $('#datatable').dataTable();

        $('#datatable-keytable').DataTable({
            keys: true
        });

        $('#datatable-responsive').DataTable();

        $('#datatable-scroller').DataTable({
            ajax: "js/datatables/json/scroller-demo.json",
            deferRender: true,
            scrollY: 380,
            scrollCollapse: true,
            scroller: true
        });


        $('#datatable-fixed-header').DataTable({
            fixedHeader: true
        });

        var $datatable = $('#datatable-checkbox');

        $datatable.dataTable({
            'columnDefs': [{
                orderable: false,
                targets: [0]
            }]
        });
        $datatable.on('draw.dt', function() {
            $('checkbox input').iCheck({
                checkboxClass: 'icheckbox_flat-green'
            });
        });

        let options = '<option value="" disabled selected>Select...</option>'
        $('#mainTableHead tr th').each( function (i) {
            if (i < 1 || i === 10) {

            }
            else {
            var title = $(this).text();
            options += `<option value="${i}" >${title}</option>`
          }
        } );

        TableManageButtons.init();

        let selectOption = 
        `
        <span class="search-in" >Search in</span>
        <select id="search-option">
        ${options}
        </select>
        `
        $('div#datatable-products_filter').append(selectOption);


        var column_no = 0;
        $('#search-option').on('change',function(){
            column_no = Number($(this).val());
        });
        
        $( 'input.form-control.input-sm' ).on( 'input', function () {
            if ( table.columns([column_no]).search() !== $( 'input.form-control.input-sm' ).val() ) {
                table.columns([column_no]).search( $( 'input.form-control.input-sm' ).val() ).draw();
            }
        } );
    };

First i retrieve all the data from the server (as an array)
im building the table with all the thead and tbody (manipulating the data and determing the headers)

I'm not using SQL i'm using mongodb + nodejs server, and there is no query - i'm just getting all the docs from the DB.
the docs can be from 1 to 100k.
it is really important to me that it will be fast, i want to transfer it to server side Data table but i cant find any good documentation for nodejs server.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren

Instead of adding the array to the DOM and building the table. Try using the data option, like this example:
https://datatables.net/examples/data_sources/js_array.html

The deferRender option may then help.

Kevin

kthorngren

Also look at using columns.render to do the formatting instead of iterating the array to format first. Might save some time.

Kevin

Noxcius

@kthorngren
Thank you for the response.
i'm manipulating each data set to my needs, can i do it in the columns render method?
and please explain why your way will improve the performance.

Noxcius

@kthorngren anyway, i think i'm gonna have to switch it to server side rendering.
i need it to be super fast, (handling also 100k of documents)...

kthorngren

Read the deferRender docs to understand how it will help with Javascript data.

i'm manipulating each data set to my needs, can i do it in the columns render method?

That depends on what you are doing. Take a look at the columns.render docs to see what it does. Also this example.

The suggestion is to save one iteration though the data. Datatables loops through the data. If you also loop through the data then that is twice through the dataset.

switch it to server side rendering.

Thats a good idea.

Kevin

Noxcius

@kthorngren
The problem is that i can't find any decent docs for that - or some examples online...

tangerine

This example includes all the necessary code:
https://datatables.net/examples/data_sources/server_side.html

colin

The protocol is discussed here.

Colin

kthorngren

The problem is that i can't find any decent docs for that

What specifically are you referring to?

I've pointed you to Datatables specific docs and examples. Please provide more specific questions. If you have questions about how Datatables can render your data then please provide an example of the data and how you would like it rendered.

Kevin

Noxcius

@kthorngren
First i'm fetching all products from the server (can be 1000 docs, can be 50k docs).
then i'm sending them to function that sets the table -

activeDashboardManager.SetProductsTable = function (data) {
  activeDashboardManager.productsManipulation(data);
  activeProductPageManager.dataInfo = data.products;
  var dataInfo = activeProductPageManager.dataInfo;
  var contentBody = "";
  let DemoStyle = "";
  var contentHead =
    "<tr>" +
    "<th class='not-export noSort' >Published</th>" +
    "<th>ID</th>" +
    "<th>Model</th>" +
    "<th>Length</th>" +
    "<th>Color</th>" +
    "<th>Visible</th>" +
    "<th>Width</th>" +
    "<th>Discount</th>" +
    "<th>T.Price</th>" +
    "<th class='not-export' >Sync</th>" +
    "</tr>";
  for (var i = 0; i < dataInfo.length; i++) {
    const productRow = activeDashboardManager.createProductRow(dataInfo[i]);
    contentBody += productRow;
  }
  activeProducts = dataInfo;

  $("#mainTableHead").html(contentHead);
  $("#mainTableBody").html(contentBody);
  $("#pagination").pagination({
    dataSource: activeProducts,
    showNavigator: true,
    pageSize: 30,
    className: "paginationjs-theme-blue paginationjs-big",
    callback: function (data, pagination) {
      let card = "";
      for (var i = 0; i < data.length; i++) {
        card += activeDashboardManager.buildProductsGridView(data[i]);
      }
      $(".grid-view-results").html(card);
    },
  });
  init_DataTables();
};

each product going some small manipulation - and then i build a table row for each product with this function -

activeDashboardManager.createProductRow = function (product) {
  newProductClass = "";
  var inputDate = new Date(product.createdAt);
  let statusText = "Channel is not active";
  let isChecked = "";
  let activeMarket = "";
  if (product.hasOwnProperty("market")) {
    if (product.market && activeProductPageManager.companymarket) {
      statusText = "Channel is active";
      isChecked = "checked";
    }
    activeMarket = product.market ? "activeMarket" : "unActiveMarket";
  }
  // Get today's date
  var todaysDate = new Date();

  if (inputDate.setHours(0, 0, 0, 0) == todaysDate.setHours(0, 0, 0, 0)) {
    newProductClass = "newProduct";
  }
  if (
    product.productdImage &&
    product.productdImage !== "" &&
    product.productdImage !== "undefined" &&
    product.productdImage !== null
  ) {
    product.productdWithImage = "../images/icons/media.svg";
  } else {
    product.productdWithImage = "";
  }

  if (!product.channels) {
    product.channels = [];
  }
  var productType = product.productdStatus === "manual" ? "---" : "auto";
  var productTypeClass =
    product.productdStatus === "manual"
      ? `
      border-right: 3px solid #fadf51;
    `
      : `
      border-right: 3px solid #2ecc71;
      color: #54c38a;
      font-weight: bold;
    `;
  let channelmarket = product.hasOwnProperty("market") && product.market;
  var productRow =
    `<tr onclick="activeDashboardManager.openProductPopup('${product._id}');" class="productRow ${newProductClass}" id="${product._id}">` +
    `<td onclick="activeDashboardManager.saveChannels('${product.id}');" >
      <div style="margin-left:0px;" class="channel-status-container" >
      <li  class="tg-list-item">
        <input id="${
          product.id + "_" + "toggle"
        }" ${isChecked} type="checkbox" class="tgl tgl-ios ${activeMarket}" />
        <label for="${product.id + "_" + "toggle"}"  class="tgl-btn"></label>
          </li>
        </div>
      </td>` +
    "<img class='editChannelsButton' src='../images/icons/globe.svg'/>" +
    `<span class="availableChannels ${product._id} ${
      channelmarket ? "activatedChannel" : ""
    }"> (${
      activeProductPageManager.companymarket && channelmarket ? "1" : "0"
    }) </span>` +
    `<td style="position: relative">
        <div class="img-id-container" >
        <img class="productd-with-image" src='${product.productdWithImage}' />
        <span class="productd-id airbnbCerealAppBook" >
          ${product.productId}
        </span>
        </div>
      </td>` +
    "<td>" +
    product.shape +
    "</td>" +
    "<td>" +
    product.weight +
    "</td>" +
    "<td>" +
    product.color +
    "</td>" +
    "<td style='text-transform: capitalize;' >" +
    (product.fancyColorIntensity ? product.fancyColorIntensity : "---") +
    "</td>" +
    "<td>" +
    product.clarity +
    "</td>" +
    "<td>" +
    product.lab +
    "</td>" +
    "<td class='markupPrice' >" +
    product.discountString +
    "</td>" +
    "<td class='markupPrice' >" +
    product.priceString +
    "</td>" +
    '<td style="' +
    productTypeClass +
    '">' +
    productType +
    "</td>" +
    "</tr>";
  return productRow;
};

each product gets he's own row and then im appending the tablehead and tablebody -

  $("#mainTableHead").html(contentHead);
  $("#mainTableBody").html(contentBody);

and than

  init_DataTables();

that's the flow it needs to go.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

It looks like you're forming a HTML row. You shouldn't need to do that if you just render the data like this example here. And if you use serverSide, then it would only be rendering those rows on the visible page.

Colin

Noxcius

@colin but i need to be that way...
i can't understand how is your example can help me achieve what i want