display more than 200,000 records in server-side processing

emma_swan

hi, i have a datatable server-side inside a success ajax like this

$('#tb_view_reports').DataTable({
                        columnDefs: [
                            { className: "text-center", "targets": [ 6,7,8 ] }
                          ],
                        processing: true,
                        serverSide: true,
                        bPaginate:   false,
                        bFilter : false,
                        initComplete: function(settings, json) {
                            $('.dataTables_scrollBody thead tr').css({visibility:'collapse'});
                        },
                        ajax: function(data,callback,settings){
                            var out = [];
                            var c = 0;

                            for (var j = 0; j < datas.length; j++) {
                                for ( var i=0; i<datas[j].length ; i++ ) {
                                    out.push([ 
                                                datas[j][i]['BranchName'],
                                                datas[j][i]['ItemGroup'],
                                                datas[j][i]['ItemCategory'],
                                                datas[j][i]['ItemLine'],
                                                datas[j][i]['ItemCode'],
                                                datas[j][i]['ItemDesc'],
                                                datas[j][i]['ItemPrice'],
                                                datas[j][i]['ItemBalance'],
                                                datas[j][i]['ItemAmount']
                                            ]);
                                };
                                c = c+ datas[j].length;
                            };

                            //console.log(datas[0]);

                            setTimeout(function(){
                                callback({
                                    draw: data.draw,
                                    data: out,
                                    recordsTotal: c,
                                    recordsFiltered: c
                                });
                            },50);
                        },
                        scrollY: 400,
                        scroller: {
                            loadingIndicator: true
                        },
                        stateSave: true
                    });

and the controller loads an json array passing it to ajax. my result datatable is saying 'Showing 1 to 46,242 of 46,242 entries'
meaning the datatable loads all the data in one. where it should say 'Showing 1 to 52 of 46,242 entries' for example if the loaded data is from 1-52 and so on like the example here https://datatables.net/extensions/scroller/examples/initialisation/server-side_processing.html that said can load up to 5millions records.

now the problem is that it can no longer load 200,000 records and more. the whole browser snaps. my question is, is there a code for datatable that i missed? please help me. thankyou so much in advance.

colin

Hi @emma_swan ,

You've set "bPaginate" to false, meaning all the data will be on a single page. If you remove that line, you should be good,

Cheers,

Colin

emma_swan

hi @colin ,

i've removed the bpaginate and the result became like this where it show 1 to 10 but still loaded all rows. please help. thankyou

colin

Hi @emma_swan ,

The thing to check then is that the server is sending back the correct data - it should only be sending back what was requested. If you look at the Ajax tab on this page here, you can see what's expected. Can you verify yours is sending correctly,

Cheers,

Colin

emma_swan

hi @colin ,

im currently using codeigniter 2 and in my controller i echo the json_encode containing the array with 40k + data. maybe my mistake is that i echo the data all together then fetch it thru ajax. but i dont know how to split the data for example by 10 in datatables. thankyou for replying,

colin

Yep, it sounds like that controller is where you need to make the change. It should only be returning the requested data. I don't know anything about CodeIgniter I'm afraid, but this link here looks like it will useful to you,

Cheers,

Colin

emma_swan

hi @colin ,

thankyou for your help. very much appreciated. i'll study the link you gave me and will update you if it is successful. more power!