Cannot get large dataset to load fast

Tronik

Hi!

I use Datatables for use of stock inventory which consists of rather large datasets, 200k rows or more.

I have problems with the data not loading as fast as it should, I've checked the SQL-query by writing a 'test.json' file, and that was super fast, same query as used in datatables.

What am I missing?

I've already tried removing almost every setting below except the sAjaxSource and sServerSide.

My only idea right now is that maybe my json is not perfectly formatted for Datatables? I dont have recordsTotal, recordsFiltered etc at the top of my json, does that affect in any way?

Thanks in advance for suggestions, and I would also like to take the oppurtunity to say that both Datatables and Allan are awesome!

"dom": 'Blrtip',
        "stateSave": true,
        "buttons": [
             {
            extend: 'colvis',
            text: 'Visa/dölj kolumner'
            },
            {
                extend: 'excelHtml5',
                title: '<?PHP echo "Lager_".date("Y-m-d"); ?>',
                footer: true
            },
        ],
        "sAjaxSource": "json.php?userid="+userid+"&stores="+stores+"&search="+test, 
        "sServerSide": true,
        "bSortClasses": false,
        "deferRender": true,
        "search": {
            "smart": true
            },
        "sAjaxDataProp": "",
        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
        "iDisplayLength": 25,
        "language": {
        "processing": "<div style='position:absolute; z-index:999; top:100px; left:100px; width:100px;'><i class='fa fa-spinner fa-5x faa-spin animated'></i></div>"
        },
        "language": {
            "url": "../dataTables.swedish.lang"
        },
        "columnDefs": [
            { responsivePriority: 1, targets: 0 },
            { responsivePriority: 2, targets: 3 }
        ],
        "processing": true,
        "fixedHeader": {
            header: true,
            footer: true
        },
        "order": [[ 0, "desc" ]],
        
        "aoColumns": [
                
            {

                        },

etc.......................

],
        "footerCallback": function( tfoot, data, start, end, display ) {
                var api = this.api();
                $( api.column( 3 ).footer() ).html(
                    api.column( 3 ).data().reduce( function ( a, b ) {
                        var numb =  Number(a) + Number(b);
                        return numb.toFixed(0);
                    }, 0 )
                );
              }

allan

Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.

Information on how to create a test page, if you can't provide a link to your own page can be found here.

Thanks,
Allan

Tronik

Ok, I will try to produce a test page!

However, can you tell me if the json formatting affects speed? I've seen some using 'data' as a 'header' in the json, some have totalrows etc.

allan

It makes basically no difference. Only if each data row had deeply nested data might you notice a difference, but even then, that is unlikely to be a major contributing factor.

Allan