Serverside-Ajax-RemoteDomain-EncodeURI

testingcartodb

Hi everyone,
firstly thanks for the time in helping me solve my problem... I feel like its very easy and very obvious... however I'm hitting a brick wall.

I have a datatable working fine, except the data is getting large some up to 20,000 rows.
I;m getting massive delays in load time as it is returning all the data and then drawing the table.

I need to get this table to work server-side I believe.

My ajax request URL is an encodeuri to carto.com and revives the databack, but I always get the full data not pagination as I see in the examples here.

I feel like my SQL is the issue but I'm hoping for some assistance.

Sample EncodeURI below = datatablesource
https://philk.carto.com/api/v2/sql?q=SELECT%20*%20FROM%20sampledata

`function createHeaderTable() {
console.log(datatablesource);
console.log(datatable_count_source);

header_d_table = $('#example-datatable').DataTable({
    pageLength: 25,
    lengthMenu: [
        [10, 25, 50, 100, -1],
        [10, 25, 50, 100, "All"]
    ],

    stateSave: false,
    processing: false,
    serverSide: true,
    sloadingRecords: false,
    deferRender: true,
    responsive: true,

    ajax: {
        url: datatablesource,
        type: 'post',
        dataSrc: 'rows',
    },
    rowCallback: function (row, data) {
        if ($.inArray(data.DT_RowId, selected) !== -1) {
            $(row).addClass('selected');
        }
    },
    initComplete: function (settings, data) {

        full_data = data;

        var LengthSum_1 = 0;
        var LengthSum_2 = 0;

        for (var i = 0; i < data.rows.length; i++) {
            if (data.rows[i]) {
                var value = parseFloat(data.rows[i].survey_length);
                if (!isNaN(value)) {
                    LengthSum_1 += value;
                }

                value = parseFloat(data.rows[i].pipe_length);
                if (!isNaN(value)) {
                    LengthSum_2 += value;
                }
            }
        }
        if (!$('#example-datatable_info .summary').length > 0) {
            $('#example-datatable_info').append('<div class="summary survey-length-summary" title="Length1"></div>');
            $('#example-datatable_info').append('<div class="summary pipe-length-summary" title="Length2 "></div>');
            $('#example-datatable_info').append('<div class="summary var-length-summary" title="Difference"></div>');
        }


        var summaryElement = $('#example-datatable_info .summary');
        $('#example-datatable_info .survey-length-summary').html('Surveys : ' + (Math.round(LengthSum_1 * 10) / 10).toLocaleString() + 'm');
        $('#example-datatable_info .pipe-length-summary').html('Assets : ' + (Math.round(LengthSum_2 * 10) / 10).toLocaleString() + 'm');
        $('#example-datatable_info .var-length-summary').html('Variance : ' + ((Math.round(LengthSum_1 * 10) / 10) - (Math.round(LengthSum_2 * 10) / 10)).toLocaleString() + 'm');

        updateData();

        loading.stop('getDataForDatatables');
        console.log("Datatable loading done");
    },
    //order : [[ 23, 'desc' ], [ 83, 'desc' ]],
    order: [
        [23, 'desc'],
        [3, 'desc'],
        [30, 'desc']
    ],
    //dom: 'lBfrtip',       
    dom: '<"top"iplBf>rt<"bottom"ip><"clear">',

    buttons: [
        /*          {
        extend : 'csv',
        className : 'btn btn-default dt-button csv',
        exportOptions : {
        columns : [0,1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,25,27,28,29,30,31,32,33,34,35,36,37,40,41,42,43,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78]
        }
        }, */
        {
            text: '<i id="excel_export_button" title="Download Survey Results" class="fa fa-lg fa-file-excel-o"></i>',
            extend: 'excel',
            className: 'btn btn-success dt-button excel',
            exportOptions: {
                columns: [0, 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 40, 41, 42, 43, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79],
                orthogonal: 'export'
            }
        }
    ],
    columns: header_d_table_columns
});

}
`
Hoping to hear back on what can be done to improve this performance.
I do need to be able to search the entire dataset which I assume would just be another AJAX call on search.

Look forward to hearing from you
Thanks

testingcartodb

Forgot to mention the site also uses codeignitor

colin

If it's returning all the data, then it's not complying with the request - it should only return the data requested, which at most would be a page length. I'd suggest looking at the protocol, it's discussed here. Also see examples here.

Cheers,

Colin