datatables loads data long time

joeshoshana

Hi Everybody,

I'm using ASP.NET to get data from server parse to json and load it to a html page.
after parsing and send the object to a html page, the page got stuck for a few second, due to the loading of all data, and after that works fine. i'm loading 12399 rows from database into the datatable it's taking like 5-6 seconds until i can work with the page again(meanwhile the page is stuck)

Is there a way to make it faster? any other ways to make the page unstuckable?

Thank you in advance.

I'm using datatables here's the code:

function loadDataToTable(data) {

// var jsonedData = JSON.parse(data)
console.log("done.")


dt = $('#table').DataTable({
    "order": [[0, "desc"]],
    "bStateSave": true,
    "fnStateSaveParams": function (oSettings, sValue) {

        $.cookie("CompanySearch", $("#CompanySearch").val(), { expires: 7 });
        $.cookie("PhoneSearch", $("#PhoneSearch").val().replace('-', ''), { expires: 7 });
        $.cookie("TreatmentByFilterDropDown", $("#TreatmentByFilterDropDown").val(), { expires: 7 });
        $.cookie("LastTreatmentByID", $("#LastTreatmentByID").val(), { expires: 7 });
        $.cookie("ReferenceSourceFilterDropDown", $("#ReferenceSourceFilterDropDown").val(), { expires: 7 });
        $.cookie("ProductClassificationFilterDropDown", $("#ProductClassificationFilterDropDown").val(), { expires: 7 });
        $.cookie("ImportanceFilterDropDown", $("#ImportanceFilterDropDown").val(), { expires: 7 });
        $.cookie("openStart", $("#openStart").val(), { expires: 7 });
        $.cookie("openEnd", $("#openEnd").val(), { expires: 7 });
        $.cookie("treatmentStart", $("#treatmentStart").val(), { expires: 7 });
        $.cookie("treatmentEnd", $("#treatmentEnd").val(), { expires: 7 });
        $.cookie("lastTreatmentStart", $("#lastTreatmentStart").val(), { expires: 7 });
        $.cookie("lastTreatmentEnd", $("#lastTreatmentEnd").val(), { expires: 7 });
        $.cookie("StatusFilterDropDown", $("#StatusFilterDropDown").val(), { expires: 7 });


        //alertify.log("States Saved");
    },
    "fnStateLoadParams": function (oSettings, oData) {
        $("#CompanySearch").val($.cookie("CompanySearch"));
        $("#PhoneSearch").val($.cookie("PhoneSearch"));
        $("#TreatmentByFilterDropDown").val($.cookie("TreatmentByFilterDropDown"));
        $("#LastTreatmentByID").val($.cookie("LastTreatmentByID"));
        $("#ReferenceSourceFilterDropDown").val($.cookie("ReferenceSourceFilterDropDown"));
        $("#ProductClassificationFilterDropDown").val($.cookie("ProductClassificationFilterDropDown"));
        $("#ImportanceFilterDropDown").val($.cookie("ImportanceFilterDropDown"));
        $("#openStart").val($.cookie("openStart"));
        $("#openEnd").val($.cookie("openEnd"));
        $("#treatmentStart").val($.cookie("treatmentStart"));
        $("#treatmentEnd").val($.cookie("treatmentEnd"));
        $("#lastTreatmentStart").val($.cookie("lastTreatmentStart"));
        $("#lastTreatmentEnd").val($.cookie("lastTreatmentEnd"));
        $("#StatusFilterDropDown").val($.cookie("StatusFilterDropDown"));

        //alertify.log("States load");
    },
    "sDom": 'T<"clear">lfrtip',
    "oTableTools": {
        "sSwfPath": "/Scripts/DataTables-1.10.4/extensions/TableTools/swf/copy_csv_xls.swf",
        "aButtons": [
            {
                "sExtends": "xls",
                "sButtonText": "Save Excel",
                "mColumns": "visible"
            }
        ]
    },
    "pagingType": "full_numbers",
    "language": {
        //"url": "dataTables.hebrew.lang"
        "sProcessing": "מעבד...",
        "sLengthMenu": "הצג _MENU_ פריטים",
        "sZeroRecords": "לא נמצאו רשומות מתאימות",
        "sInfo": "_START_ עד _END_ מתוך _TOTAL_ רשומות",
        "sInfoEmpty": "0 עד 0 מתוך 0 רשומות",
        "sInfoFiltered": "(מסונן מסך _MAX_  רשומות)",
        "sInfoPostFix": "",
        "sSearch": "חיפוש חופשי:",
        "sUrl": "",
        "oPaginate": {
            "sFirst": "ראשון",
            "sPrevious": "קודם",
            "sNext": "הבא",
            "sLast": "אחרון"
        }
    },
    "aaData": data,
    "columns": [
        { "mDataProp": "ID" },
        { "mDataProp": "OpenDate" },
        { "mDataProp": "NextContactDay" },
        { "mDataProp": "CompanyName" },
        { "mDataProp": "ContactName" },
        { "mDataProp": "Phone" },
        { "mDataProp": "CellPhone" },
        { "mDataProp": "Details" },
        { "mDataProp": "Status" },
        { "mDataProp": "TreatmentBy" },
        { "mDataProp": "TreatmentByID" },
        { "mDataProp": "ReferenceSourceID" },
        { "mDataProp": "ProductClassificationID" },
        { "mDataProp": "ImportanceID" },
        { "mDataProp": "StatusID"  },
        { "mDataProp": "LastTreatmentDate" },
        { "mDataProp": "LastTreatmentBy" },
        { "mDataProp": "LastTreatmentByID" }
    ],
    "columnDefs": [
        //{ "searchable": false, "targets": [8,9,10,14,15,16,17]}, // set this cols un searchable
        { "width": "10%", "targets": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17] },
        {"type" : "date", "targets": [1]},
        {
            "targets": [10],
            "visible": false,
        },
        {
            "targets": [11],
            "visible": false
        },
        {
            "targets": [12],
            "visible": false
        },
        {
            "targets": [13],
            "visible": false
        },
        {
            "targets": [14],
            "visible": false
        },
        {
            "targets": [17],
            "visible": false
        },
        {
            "targets": [16],
            "visible": false
        },
        {
            "targets": [15],
            "visible": false
        }
    ],
    "fnInitComplete": function () {
       ////alertify.log("COMPLETE");
       // if ($('.dataTables_filter').length == 1) {
       //     $('#filterDiv').append($('.dataTables_filter'));
       // } else {
       //     $('.dataTables_filter')[0].remove();
       //     $('#filterDiv').append($('.dataTables_filter')[0]);
       // }
        checkForHiddenFilters();
        $($('.panel')[1]).append($('.DTTT_container'));
        //$('#table_filter').appendTo($('#filterDiv'));
        // //var span = $('<span />');
       // //span.addClass("glyphicon glyphicon-chevron-left");
       // //span.attr('data-toggle', 'modal');
       // //span.attr('data-target', '.bs-example-modal-lg');
       // //var secondSpan = $('<span />');
       // //secondSpan.addClass("glyphicon glyphicon-remove");
       // //secondSpan.on('click', function () {
       // //    fnResetAllFilters();
       // //});
       // $('.dataTables_filter > label > input').addClass("form-control");
       //// $('.dataTables_filter').append(secondSpan);
       // //$('.dataTables_filter').append(span);

    }
});

};

glenderson

deferRender might be a solution.
Switching to serverSide would be a solution.

Are you 100% sure that it's loading dataTables that is taking the time? Or is writing the json response taking time? Have you recorded programmatically the time that it takes to open the database and read through all the records? Your data appears to be a customer type list and with 12000+ records are you inner joining and missing indexes in the database?