Ajax function with millions of data

abuabdillah

I have a search page with datatable with millions of rows of data fetched from the database.
I am stuck how to use the ajax data with deferRender and passing as datasets to the datatable.

See my datatable code below, then i have three queries:
1. I would like to know how to pass the iterated and interval data to the datatable using callback???
2. How to pass the image column to pdf export? (the dataURL in the below code is not yeilding expected result)
3. And what all methods can be used to make the process faster?

Datatable code:

$('#resultTable').DataTable( {                      
                destroy: true,  
                "autoWidth": false,    
                processing: true,
                "serverSide": true,             
                //"aaData": result,  // THIS IS WORKING IF THE AJAX RESULT GIVEN DIRECTLY, BUT LOADING WILL TAKE MUCH TIME
                ajax: {
                    type: 'POST',
                    url: 'search.php',
                    dataType: 'json',
                    data: { durationFrom: durationFrom, durationTo: durationTo, type: type, color: color, city: city, number: number },      
                    success:function(result){ 
                        data = result;
                        var out = [];
         
                        for ( var i=0; i<data.length ; i++ ) {
                            out.push( [ data[i].id, data[i].eventtime, data[i].number, data[i].imageURL ] );
                        }
                
                        setTimeout( function () {
                            callback( {
                                draw: data.draw,
                                data: out,
                                recordsTotal: data.length,
                                recordsFiltered: data.length
                            } );
                        }, 50 );
                    }                       
                },
                "aoColumns": [
                    { "sTitle": "Id",
                    "mDataProp": "id",
                     "sWidth" : "50px" },                               
                    { "sTitle": "Date",
                    "mDataProp": "eventtime",
                     "sWidth" : "120px" },
                    {"sTitle": "Number",
                     "mDataProp": "number",
                     "sWidth" : "50px" },                  
                    {"sTitle": "Image",
                     "mDataProp": "imageURL",
                        "render": function(mDataProp, type, row) {
                            return '<img src="'+mDataProp+' " alt="No image available" />';
                            },
                     "sWidth" : "50px"
                    }
                ],
                deferRender:    true,
                scrollY:        400,
                "bSortClasses": false,
                info: true,
                language: {
                        "zeroRecords": "No matching records found - Please search with different search criteria",
                            "emptyTable": "No data available in table",
                        "infoEmpty": "No entries to show",
                            "info": "Records _START_ to _END_ of _MAX_",
                        "loadingRecords": "Loading...",
                        "Processing":  "<img src='images/loader.gif' alt='Processing... Please wait...'>",
                        "LoadingRecords": 'Loading...',
                        "search":         "Search:",
                        "paginate": {
                            "first":      "First",
                            "last":       "Last",
                            "next":       "Next",
                            "previous":   "Previous"
                        },
                    },
                 aaSorting : [[0, 'desc']],
            "pagingType": "input",  
                "pageLength": 100,
                "lengthMenu": [[100, 500, 1000, 10000], [100, 500, 1000, 10000]],
                dom: 'Blfrtip',
                buttons: [
                    'copy',
                    {
                        extend: 'csv',
                        title: myFileName
                    },
                    {
                        extend: 'excelHtml5', 
                        title: myFileName
                    },
                     'print' , 
                    {
                        extend: 'pdfHtml5', 
                        title: myFileName  ,
                        exportOptions: {
                            columns: [0, 1, 2, 3, 4],
                            stripHtml: false
                        },
                        customize: function (doc) {

                            for (var i=1;i<doc.content[1].table.body.length;i++) {

                                var imageMarkUp = doc.content[1].table.body[i][4].text;
                                var srcImg = $(imageMarkUp).attr('src');


                                var img = new Image,
                                    canvas = document.createElement("canvas"),
                                    ctx = canvas.getContext("2d"),
                                    src = srcImg; 

                                img.crossOrigin = "Anonymous";

                                img.onload = function() {
                                    canvas.width = img.width;
                                    canvas.height = img.height;
                                    ctx.drawImage( img, 0, 0 );
                                    localStorage.setItem( "savedImageData", canvas.toDataURL("image/png") );
                                }
                                var dataURL = canvas.toDataURL("image/png");                                            


                                if (doc.content[1].table.body[i][4].text == img.src) {
                                    delete doc.content[1].table.body[i][4].text;
                                    doc.content[1].table.body[i][4].image = 'img';
                                }
                            }

                            doc.content.splice(4, 0, {
                                margin: [0, 0, 0, 12],
                                alignment: 'center',
                                image: dataURL
                            });
                        }
                    }
                ]
            }); 

abuabdillah

Anyone to help??

allan

For server-side processing use server-side processing. The deferRender option is irrelevant for server-side processing, it has no effect.

If you are having problems with it, please link to a test page showing the issue, as per the forum rules.

Allan

abuabdillah

HI Allan,

Thank you for your reply.

Right now, i dont have my app online. Sorry for that, any way let me check if i can make a test page online.

For the callback part, error throwing like callback is not defined, how to use the callback?

allan

Information on how to create a test page, if you can't provide a link to your own page can be found here.

Thanks,
Allan