Performance with Editor when setting a value on all filtered rows of table

nsscott

Hi,

In the screenshot you can see how I've added an Apply button which, when clicked, updates a column of filtered results in the datatable. Updating a table of 484 entries can take nearly a minute on my machine. The .edit() function seem to be taking the majority of the time. Is there a way to speed this up?

var defaultPairsPerCase;
$('#ApplyPairsPerCase').click(function () {
    defaultPairsPerCase = $("#DefaultPairsPerCase").val();
    mainTable.rows({ filter: 'applied' }).every(function (rowIdx, tableLoop, rowLoop) {
        var row = mainTable.rows(rowIdx);
        mainEditor.edit(row, false);
        smainEditor.set('PairsPerCase', defaultPairsPerCase);
        mainEditor.submit();        
    });
});

rf1234

Bulk editing is a problem with Editor. I have one page where I do this - and with very large records as well. I had to implement custom warnings to make sure my users understand what's going on ...

In your use case it would be best to just do an ajax call to send an array of the ids of your records to the server for custom processing. Here is an example from the data table I mentioned. The users can select or deselect rows for batch processing in a special interface. Since Editor is much too slow for these database updates I defined a custom button that does an ajax call to handle this server side.

//custom button to put all selected contracts in to the accounting interface
$.fn.dataTable.ext.buttons.selectedInterfaceFalse = {
    extend: 'selected',
    text: "Selected not in Interface",
    name: "selectedInterfaceFalseButton",
    action: function ( e, dt, button, config ) {
        var selected = dt.rows( {selected: true} ).data().toArray();
        var contractIdArray = [];
        $.each(selected, function(key, value) {
            contractIdArray.push(value.contract.id);
        })
        if ( contractIdArray.length > 0 ) {
            $.ajax({
                type: "POST",
                url: 'actions.php?action=acctInterfaceFalse',
                data: {
                    contractIdArray: JSON.stringify(contractIdArray)
                },
                success: function () {
                     .... e.g. do an ajax reload of the data table ...
                }
            });
        }
    }
};

rf1234

As an additional hint for performance improvements in bulk processing (though probably not relevant in your current use case):

If you can avoid using "every" and pass an array to a Data Tables or Editor method instead of an individual value multiple times this will speed things up dramatically. Below you can see two custom buttons that do the same. The first one is fast - the second one is really slow because it calls "select()" and "deselect()" hundreds of times (in my use case).

//custom button to select all rows that are in the accounting interface
$.fn.dataTable.ext.buttons.selectAllInterface = {
    text: selectAllInterfaceLabel,
    name: "selectedAllInterfaceButton",
    action: function ( e, dt, button, config ) {
        // var sRows = dt.rows({ search: 'applied' });
        var sRows = dt.rows({ search: 'applied' }).data().toArray();
        var interfaceArray = [];
        var notInterfaceArray = [];
        $.each(sRows, function(key, value) {
            if ( value.contract_has_infoma.include > 0 ) {
                interfaceArray.push("#" + value.DT_RowId);
            } else {
                notInterfaceArray.push("#" + value.DT_RowId);
            }
        })
        dt.rows(interfaceArray).select();
        dt.rows(notInterfaceArray).deselect();
    }
};

//custom button to select all rows that are in the accounting interface using "Every"
$.fn.dataTable.ext.buttons.selectAllInterfaceEvery = {
    text: selectAllInterfaceLabel + '_Every',
    name: "selectedAllInterfaceButton",
    action: function ( e, dt, button, config ) {
        dt.rows({ search: 'applied' }).every( function ( rowIdx, tableLoop, rowLoop ) {
            var data = this.data();
            if ( data.contract_has_infoma.include > 0 ) {
                dt.row(this).select();
            } else {
                dt.row(this).deselect();
            }
        });
    }
};

nsscott

Thank you for the replies!

I"m not sure if this is relevant but in your first post you mention "Since Editor is much too slow for these database updates". I wanted to be clear that the table is completely local; no ajax or other connectivity. The only sever interaction occurs when i click the save button which I handle manually. Is that relevant?

I'm in interested in the performance gains you mention in your second post. Is it possible to edit multiple rows at once? That is can I do something like:

dt.rows(interfaceArray).edit().set('PairsPerCase', defaultPairsPerCase);

rf1234

I wanted to be clear that the table is completely local; no ajax or other connectivity. The only sever interaction occurs when i click the save button which I handle manually. Is that relevant?

Amazing that Editor is that slow in bulk editing even without server calls. But then the solution is even easier for you. Why use Editor if you only manipulate the values of a local Data Table? You can do it like this as well. And this is fast I use the same logic myself.

var defaultPairsPerCase;
$('#ApplyPairsPerCase').click(function () {
    defaultPairsPerCase = $("#DefaultPairsPerCase").val();
    mainTable.rows({ filter: 'applied' }).every(function (rowIdx, tableLoop, rowLoop) {
        var data = this.data();
        data.PairsPerCase = defaultPairsPerCase; // update data source for the row
        this.invalidate(); // invalidate the data DataTables has cached for this row
    });
    // Draw once all updates are done
    mainTable.draw();
});

Take a look at this as well please: https://datatables.net/reference/api/row().data()

I'm in interested in the performance gains you mention in your second post. Is it possible to edit multiple rows at once? That is can I do something like:

dt.rows(interfaceArray).edit().set('PairsPerCase', defaultPairsPerCase);

You can do bulk editing using an Editor form as well. Just by selecting multiple records with the select extension. I do that as well - and Editor is slow because for every record a server call is being made - or in your case: a local manipulation.

allan

Is it possible to edit multiple rows at once?

Yes indeed. Something like this should do it:

$('#ApplyPairsPerCase').click(function () {
    var defaultPairsPerCase = $("#DefaultPairsPerCase").val();
    var indexes = mainTable.rows({ filter: 'applied' }).indexes();

    mainEditor
        .edit(indexes, false);
        .set('PairsPerCase', defaultPairsPerCase);
        .submit();        
});

Allan

nsscott

So the good news is not using the editor worked great....lightening fast.

I tried the code below and two strange things. The first is the performance was no better...the .edit() function took a long time to complete. The second is my hour glass didn't show...any thoughts on why this might be?

    $('#ApplyPairsPerCase').click(function () {    
        $('#hourglass').show();
        var defaultPairsPerCase = $("#DefaultPairsPerCase").val();
        var indexes = mainTable.rows({ filter: 'applied' }).indexes();

        mainEditor
            .edit(indexes, false)
            .set('PairsPerCase', defaultPairsPerCase)
            .submit();

        for (var i = 0 ; i < indexes.length; i++) {
            FlagRowAsModified(mainTable.rows(i).data()[0].OrderDetailId);
        }
        $('#hourglass').hide();
    });

rf1234

So the good news is not using the editor worked great....lightening fast. .... The first is the performance was no better...the .edit() function took a long time to complete.

I thought it would be that way. I had the same issue with "regular" bulk editing (i.e. selecting many records and then hit the "Edit" button for form editing). I suspected it was due to the many ajax calls but that obviously isn't the (only) reason. Thanks for confirming!

The hourglass doesn't show because of JS's asynchronous nature: The Editor takes so long - and your code doesn't wait for it to finish - that the hourglass may only be a short blinking or something. You would need to use a callback or a promise for it to work if you want to use Editor. I also added a short timeout.

This might work (didn't test it):

$('#ApplyPairsPerCase').click(function () {   
    $('#hourglass').show();    
    setTimeout(function() {
        var defaultPairsPerCase = $("#DefaultPairsPerCase").val();
        var indexes = mainTable.rows({ filter: 'applied' }).indexes();
        mainEditor
            .edit(indexes, false)
            .set('PairsPerCase', defaultPairsPerCase)
            .submit()
            .promise().done(function() {
                for (var i = 0 ; i < indexes.length; i++) {
                    FlagRowAsModified(mainTable.rows(i).data()[0].OrderDetailId);
                }
                $('#hourglass').hide();
            } );
    }, 50);
});