Add 500,000 rows to a table using JavaScript without crashing the page

Question

I have a report system where the user clicks the report, the SQL is executed and the results are shown in a table. The problem I have is a single report brings back over 500,000 rows for the report. The system takes a while to retrieve the data via AJAX call, however as soon as that has finished the browser "hangs" whilst it is adding the HTML to the page.

My question is this, is there another way I can add the HTML to the page without causing the browser to hang?

var HTMLPRE = '<p class="text-center"><b id="lblReportStatus">Producing report...</b><br><small>Average time: ' + parseFloat($('#hidReportID').attr('data-avg')).toFixed(2) + ' seconds</small>' +
    '<br><small>Current time: <span id="divCurrentTimeTaken">0</span></small></p>';
window.CurrentTimer = 0;
$('#reportresults').html(HTMLPRE);
// start the timer
window.ProducingReportTimer = window.setInterval(function() {
    window.CurrentTimer++;
    $('#divCurrentTimeTaken').html(window.CurrentTimer + ' seconds');
}, 1000);

$.post(window.routes['ReportWriter.ProduceReport'], FormData, function(resp, status) {
    if (status === 'success') {
        if (resp.code == '200') {
            $('#lblReportStatus').text('Generating report...<br>Please note your browser may become un-responsive.  Please wait for a few minutes if this happens.');
            ProduceReportTable(resp);
        }
    } else {
        alert("Unable to produce report.  Please contact support with the below information:\r\nStatus code" + status);
    }
}).fail(function(err, status) {
    alert("Unable to produce report.  Please contact support with the below information:\r\n" + err);
});

function ProduceReportTable(resp){
    var ReportHTML = '<div class="row"><div class="col-xs-12"><button class="btn btn-primary" id="btnExportExcel"><i class="fa fa-file-excel"> Excel</i></a></div>' +
        '</div><div class="col-xs-12"><div class="table-responsive" style="overflow-x: auto;">' +
        '<table class="table-hover table-striped table" id="tblReport">' +
        '<thead><tr>';
    // loop through the headers first
    $(resp.headers).each(function (idx, head) {
        ReportHTML += '<th>' + head + '</th>';
    });

    ReportHTML += '</tr></thead><tbody>';
    // loop through the data

    $(resp.rows).each(function (idx, row) {
        ReportHTML += '<tr>';
        $.each(row, function() {
            ReportHTML += '<td>' + (this instanceof Window ? '' : this) + '</td>';
        });
        ReportHTML += '</tr>';
    });
    ReportHTML += '</tbody></table></div></div>';
    $('#reportresults').html(ReportHTML);
    window.clearInterval(window.ProducingReportTimer);
    /*
    $('#tblReport').dataTable({
        deferRender:    true,
        /*scrollY:        200,*//*
        scrollCollapse: true,
        scroller:       true
    });*/

    // enable the excel button
    $('#btnExportExcel').on('click', function(e){
        e.preventDefault();
        let TableSource = document.getElementById('tblReport');
        var today = new Date();
        var dd = today.getDate();
        var mm = today.getMonth()+1; //January is 0!
        var yyyy = today.getFullYear();
        if(dd<10) {
            dd = '0'+dd
        }
        if(mm<10) {
            mm = '0'+mm
        }
        today = yyyy + '-' + mm + '/' + dd;
        GenerateXLSX(TableSource, true, resp.ReportName + '-' + today + ".xlsx")
    });
}

Sorry if this has been answered elsewhere, I've had a search and have been unable to find anything.

Answer 1

No. Comments have already exposed great answers; the gist of it is that returning that many rows is a bad idea from a performance point (nothing can render that much data properly), from a user point (no one can possibly work with that much data at once) and from a security point (you're risking a huge DoS attack to your app). The only use case I can possibly imagine for that is creating a report, and in that case you should export an Excel/PDF document instead of showing it in HTML.

You should implement pagination and filtering options to solve your problem. If you have to use JavaScript for that, use jquery Datatables and a proper AJAX endpoint on your app.