Serverside-Ajax-RemoteDomain-EncodeURI

Serverside-Ajax-RemoteDomain-EncodeURI

testingcartodbtestingcartodb Posts: 2Questions: 1Answers: 0
edited May 2020 in Free community support

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

Answers

  • testingcartodbtestingcartodb Posts: 2Questions: 1Answers: 0

    Forgot to mention the site also uses codeignitor

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

This discussion has been closed.