editor_remove performance large data sets

Restful Web Services · December 2014

Hi,

I am using datatables, including the Editor extension, to display large data sets, up to 1,000,000 rows. I have enabled server side processing to improve the speed and this works. However, at times the ability to remove many rows at once is necessary. Using editor_remove works just about for up to 20,000 rows however once that number is passed the processing speed makes the function un-useable, in Chrome I receive the 'warning unresponsive web page' prompt asking me if I want to kill the webpage.

My datatables code is like this:

<script type="text/javascript"> 
$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        "ajax": {
            "url": "/uploads/js/datatables/php/table.cms_module_tps_userlisting.php",
            "type": "POST",           
            "data": function ( d ) {
                d.user_id = "22";
                d.checkcode = "esum";
            }
        },  
        "table": "#cms_module_tps_userlisting"
    } );
    
    $('#cms_module_tps_userlisting').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this );
    } );

    $('#cms_module_tps_userlisting').dataTable( {
        "dom": 'Tfrltlpi',
        "bSortClasses": false,
        "processing": true,
        "serverSide": true,        
        "ajax": {
            "url": "/uploads/js/datatables/php/table.cms_module_tps_userlisting.php",
            "pages": 2, // number of pages to cache
            "type": "POST",
            "data": function ( d ) {
                d.user_id = "22";
                d.checkcode = "esum";
            }
        },               
        "language": {
           "search": "_INPUT_",
           "searchPlaceholder": "Search....",
           "sLengthMenu": "_MENU_ per page"
        },
        "order": [[ 2, "desc" ]],
        "columns": [
            { 
                "data": "null", defaultContent: '', orderable: false, "bSearchable": false
            },
            {
                "data": "listphone"
            },
            {
                "data": "create_date"
            },
            {
                "data": "checkcode"
            },
            {
                "data": "registered_with_tps"
            }
        ],
        "columnDefs": [ {
            "targets": 2,
            "createdCell": function (td) {
                $(td).addClass('grey')
            }
        }],
        "tableTools": {
            "sRowSelect": "os",
            "sSwfPath" : "/uploads/js/datatables/swf/copy_csv_xls_pdf.swf",
            "aButtons": [
                "select_all", "select_none", "print",
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    "aButtons": [
                    {
                    "sExtends": "copy",
                    "sButtonText": "Copy - 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "copy",
                    "sButtonText": "Copy - 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                    
                    {
                    "sExtends": "csv",
                    "sButtonText": "CSV - Save 'Number' column only",
                    "mColumns": [1],
                    "sFieldBoundary": ''
                    },
                    {
                    "sExtends": "csv",
                    "sButtonText": "CSV - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                    
                    {
                    "sExtends": "xls",
                    "sButtonText": "Excel - Save 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "xls",
                    "sButtonText": "Excel - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },
                    {
                    "sExtends": "pdf",
                    "sButtonText": "PDF - Save 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "pdf",
                    "sButtonText": "PDF - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                     
                    ]
                },
                { "sExtends": "editor_remove", "editor": editor }
            ]
        }
    } );
} );
</script>

Is there any way to improve the performance of editor_remove or adjust the popup 'loading prompts' so that they appear immediately upon clicking the 'delete' button rather than, in the case of 30,000 rows, 5 - 6 seconds after the buttons has been clicked?

In general can anyone see anything obvious I can do to improve the overall performance of datatables?

Thanks

Chris

allan · December 2014

How are you selecting that many rows if you have sever-side processing enabled? Do you have a length menu options to show all rows (I don't see it above) or disabling paging somewhere?

Thanks,
Allan

Restful Web Services · December 2014

Hi Allan,

Yes I have a length menu, sorry I omitted it from the code above:

<script type="text/javascript"> 
$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        "ajax": {
            "url": "/uploads/js/datatables/php/table.cms_module_tps_userlisting.php",
            "type": "POST",           
            "data": function ( d ) {
                d.user_id = "22";
                d.checkcode = "esum";
            }
        },  
        "table": "#cms_module_tps_userlisting"
    } );
    
    $('#cms_module_tps_userlisting').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this );
    } );

    $('#cms_module_tps_userlisting').dataTable( {
        "dom": 'Tfrltlpi',
        "bSortClasses": false,
        "processing": true,
        "serverSide": true,        
        "ajax": {
            "url": "/uploads/js/datatables/php/table.cms_module_tps_userlisting.php",
            "type": "POST",
            "data": function ( d ) {
                d.user_id = "22";
                d.checkcode = "esum";
            }
        },
        "lengthMenu": [ [10000, 20000, 30000, 100000000], [10000, 20000, 30000, "All"] ],
        "language": {
           "search": "_INPUT_",
           "searchPlaceholder": "Search....",
           "sLengthMenu": "_MENU_ per page"
        },
        "order": [[ 2, "desc" ]],
        "columns": [
            { 
                "data": "null", defaultContent: '', orderable: false, "bSearchable": false
            },
            {
                "data": "listphone"
            },
            {
                "data": "create_date"
            },
            {
                "data": "checkcode"
            },
            {
                "data": "registered_with_tps"
            }
        ],
        "columnDefs": [ {
            "targets": 2,
            "createdCell": function (td) {
                $(td).addClass('grey')
            }
        }],
        "tableTools": {
            "sRowSelect": "os",
            "sSwfPath" : "/uploads/js/datatables/swf/copy_csv_xls_pdf.swf",
            "aButtons": [
                "select_all", "select_none", "print",
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    "aButtons": [
                    {
                    "sExtends": "copy",
                    "sButtonText": "Copy - 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "copy",
                    "sButtonText": "Copy - 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                    
                    {
                    "sExtends": "csv",
                    "sButtonText": "CSV - Save 'Number' column only",
                    "mColumns": [1],
                    "sFieldBoundary": ''
                    },
                    {
                    "sExtends": "csv",
                    "sButtonText": "CSV - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                    
                    {
                    "sExtends": "xls",
                    "sButtonText": "Excel - Save 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "xls",
                    "sButtonText": "Excel - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },
                    {
                    "sExtends": "pdf",
                    "sButtonText": "PDF - Save 'Number' column only",
                    "mColumns": [1]
                    },
                    {
                    "sExtends": "pdf",
                    "sButtonText": "PDF - Save 'All' columns",
                    "mColumns": [1, 2, 3, 4]
                    },                     
                    ]
                },
                { "sExtends": "editor_remove", "editor": editor }
            ]
        }
    } );
} );
</script>

Thanks

Chris

allan · December 2014

Hi Chris,

Okay, so if you select the 100000000 option, it is going to load all of the roads and completely negate any performance benefit you might get from using server-side processing. In fact, it will seriously harm performance since it needs to create and draw all of those rows, on every request!

How many rows are you working with? I would suggest perhaps trying client-side processing with the deferRender option enabled. You do have the hit of all rows being loaded up front (although you have that time and again at the moment with those view settings!) but only the rows needed for display will be created, and only when needed.

It should also help performance on delete.

The other option would be to have a specific "Delete all" button that sends a special flag to the server to tell it to delete all rows, rather than specifying each one individually.

Allan

Restful Web Services · December 2014

Hi Allan,

Yes I dont really want the 'all lines' function but I couldnt see any other way of being able to delete or export all rows at once from the table.

The 'special' delete all button sounds interesting. Are there any links to examples of this already in place?

Thanks

Chris

allan · December 2014

No direct example of that as it isn't something provided by the Editor libraries, but it would be easy to create with a custom fnClick action in a TableTools button. You would just make a custom $.ajax call to a page / route that will perform the detail all action.

Allan

Restful Web Services · December 2014

Hi Allan,

So I am trying to do as you suggested, however I am starting with the save function as that is more important. I am using the plugin
download (POST + GET) found here, http://www.datatables.net/extensions/tabletools/plug-ins, to create my new download 'All' button, I have no idea what I am supposed to put in my generate_csv.php php file and cannot find any working example. Is there an example of the generate_csv.php file which will create the data from my table in the CSV download file?

Thanks

Chris

allan · December 2014

Hi Chris,

I don't think there is an example of that file directly available - however, all that is needed is to query the database to get the records and then just write them out in a CSV style. Php has csv creation methods which might be useful, but you could also just echo the data out.

If you wanted the same sorting and filtering to be applied to the table as the display, you could use a common server-side processing function that both the DataTables get and the CSV creation could both call (since they both pass in basically the same parameters).

Regards,
Allan

Restful Web Services · December 2014

Hi Allan,

Thanks for the information. I am getting there now but have hit a stumblig block. I cannot seem to pass any URL parameters to generate_csv.php

This should be possible shouldn't it?

I am trying:

                {
                    "sExtends": "download",
                    "sButtonText": "Download",
                    "sUrl": "/uploads/js/datatables/php/generate_csv.php?user_id=96"
                },

But I get nothing in generate_csv.php with:

$user_id = $_POST['user_id'];
echo $user_id

I have also tried with the BUTTON function,

    "fnClick": function( nButton, oConfig ) {
        var oParams = this.s.dt.oApi._fnAjaxParameters( this.s.dt );
        var aoPost = [
            { "user_id": "96", "checkcode": "ENUM" }
        ];
        var aoGet = [];

Is there another way to pass parameters?

Thanks

Chris

allan · December 2014

$user_id = $_POST['user_id'];

I'm not surprised you get nothing with that. You are passing it as a GET parameter :-)

Allan

Restful Web Services · December 2014

Ah yes, whoops! Thanks

editor_remove performance large data sets

editor_remove performance large data sets

Answers

Howdy, Stranger!

Categories

DataTables

editor_remove performance large data sets

editor_remove performance large data sets

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables