take too long to load millions of records in datatable

@suhasini_r To deal with the number of records,

	 -  For display purposes, you should go with the pagination
     -  To export it to the PDF/Excel you should use the go with the chunking results of your dataset.

This is my blade

var detail_table= $('#detail_data-table').DataTable( { dom: 'lBfrtip', buttons: [ { "extend": 'excel', "text": 'EXCEL', "titleAttr": 'EXCEL', "action": newexportaction }, { "extend": 'csv', "text": 'CSV', "titleAttr": 'CSV', "action": newexportaction

            },
            {
                    "extend": 'pdf',
                    "text": 'PDF',
                    "titleAttr": 'PDF',
                    "orientation": 'landscape',
                    "pageSize": 'TABLOID',
                    "action": newexportaction
            }

], processing: true, serverSide: true, searching: true, ], ajax: { url: "{{ route('detailreport') }}", data: function (d) { d.detail_approved = $('#detail_approved').val(), d.detail_to_date=$('#detail_to_date').val(), d.detail_from_date=$('#detail_from_date').val(), d.detail_search=$('#detail_search').val() }

},
columns:
    [
            {data:'DT_RowIndex', name: 'DT_RowIndex', orderable: false, searchable: false, className: "text-center" },
            @if(Auth::user()->user_master_id === 1)
            { data: 'name', name: 'name', className: 'text-left', orderable: true, searchable: true },
            @endif
            {data:'campaign_name',name:'campaign_name', className: "text-left", orderable: true, searchable: true},
            {data:'dst',name:'dst', className: "text-center",  orderable: true, searchable: true},
            {data:'src',name:'src', className: "text-center", orderable: true, searchable: true},
            {data:'disposition',name:'disposition', className: "text-left",  orderable: true, searchable: true,
            createdCell: function (cell, cellData, rowData, rowIndex, colIndex)
            {
                    if (cellData === 'ANSWERED')
                    {
                            $(cell).addClass('text-bold text-success');
                    }
                    else if (cellData === 'NO ANSWER' || cellData === 'BUSY' || cellData === 'FAILED')
                    {
                            $(cell).addClass('text-bold text-danger');
                    }
            }

}, {data:'retry_count',name:'retry_count', className: "text-center", orderable: true, searchable: true}, {data:'billsec',name:'billsec', className: "text-center", orderable: true, searchable: true}, {data:'context',name:'context', className: "text-left", orderable: true, searchable: true}, {data:'calldate',name:'calldate', className: "text-center", orderable: true, searchable: true, width: '200px'}, {data:'last_call_time',name:'last_call_time', className: "text-center", searchable: true, width: '200px'}, {data:'hangupdate',name:'hangupdate', className: "text-center", searchable: true, width: '200px'}, ], });

function newexportaction(e, dt, button, config) { var self = this; var oldStart = dt.settings()[0]._iDisplayStart; dt.one('preXhr', function (e, s, data) { // Just this once, load all data from the server... data.start = 0; data.length = 2147483647; dt.one('preDraw', function (e, settings) { // Call the original action function if (button[0].className.indexOf('buttons-copy') >= 0) { $.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-excel') >= 0) { $.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-csv') >= 0) { $.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-pdf') >= 0) { $.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-print') >= 0) { $.fn.dataTable.ext.buttons.print.action(e, dt, button, config); } dt.one('preXhr', function (e, s, data) { // DataTables thinks the first item displayed is index 0, but we're not drawing that. // Set the property to what it was before exporting. settings._iDisplayStart = oldStart; data.start = oldStart; }); properly. setTimeout(dt.ajax.reload, 0); // Prevent rendering of the full data to the DOM return false; }); }); // Requery the server with the new one-time export settings dt.ajax.reload(); }

I think that you need to optimize your sql query , check please if the fields (report_status, calldate) is indexed in your db table.

Can you use whereBetween instead of whereDate ?

browsers are not built to deal with presentation of millions of records

humans are not able to sensibly look through millions of records

This is a pointless question

Query only what's needed and paginate.

• do some refactoring
• set up some relations
• study RDBMS proper techniques (suggest actually take a course)

Edit:

And I suggest not use "a datatables package", learn how to do things in a regular HTML table. I write my own inline edit and add new code. It is worth going through the learning curve to learn this stuff.

Anything more than a few hundred records will be slow(er), and when it comes to millions, it will be extremely slow, there isn't anything you can do about it, unless you paginate it as previously stated by several ppl. Exporting it to excel isn't doable if you have more than 1,048,576 (limit of Excel) records to export.

if using datatables, you should be using server queries for pagination, not trying to ship the whole result set to the client.