Search for data table is very slow

Search for data table is very slow

JaiPalJaiPal Posts: 1Questions: 1Answers: 0

We are using data table for displaying data wit 7000 rows of data and 60 columns with multi select drop down option for individual columns, when we are searching in the data table it is taking so much time for filtering, if their are any other ways to speedup the search and data loading into the data table please inform. Thank you in advance.

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Happy to take a look at a test case showing the issue so we can performance trace it.

    Allan

  • kanamitskanamits Posts: 1Questions: 0Answers: 0

    The same problem with me, my table is a server-side processing. Every time I keypress the datatables activates ajax. I suggest closing the previous ajax every time you keypress in the search bar.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    If you have server-side processing enabled, then yes, every search key press will trigger an Ajax request - that's how server-side processing works (its gets the data to display for every draw from the server). There is the searchDelay option which can be useful when server-side processing if you want to introduce a delay to allow for key presses.

    ALlan

  • rduncecbrduncecb Posts: 125Questions: 2Answers: 28

    If the problem is caused by server side reloading on each keypress you could always debounce the search box so that the ajax call will only be triggered after a short delay after the user has stopped typing rather than every keypress. I've done this before using underscorejs with this function:

            function debounceSearch(tableId) {
                var $searchBox = $(tableId + "_filter input[type='search']");
                $searchBox.off();
    
                var searchDebouncedFn = _.debounce(function() {
                    $(tableId).DataTable().search($searchBox.val()).draw();
                }, 300);
    
                $searchBox.on("keyup", searchDebouncedFn);
            }
    
    

    Calling this with something like debounceSearch('#example') will cause the ajax call to be triggered only if there is is a pause between keyup events of 300ms. While the user is typing it will wait 300ms after they have stopped (unless they type slowly with > 300ms gap between keypresses) to update the table. I've found it can reduce the number of ajax calls a bit over the searchDelay option.

  • sparc64sparc64 Posts: 1Questions: 0Answers: 0

    When using @rduncecb code noticed that it doesn't completely match default datatable's search behaviour (e.g. doesn't compare old and new value, doesn't process cut/paste)

    So, instead of doing search manually through DT API decided to detach all events from input box except 'DT.search' (which should be fine even without debouncing) and use it as original search interface.

    This way search continue working same way as original DT as we are re-using original event handler (which is actually same at the moment for all listed 'DT.' events).

    var EVENTS_TO_DEBOUNCE = 'keyup.DT input.DT cut.DT paste.DT';
    
    $('.dataTables_filter input[type="search"]')
    .off(EVENTS_TO_DEBOUNCE)
    .on(EVENTS_TO_DEBOUNCE, _.debounce(function() {
        $(this).trigger('search.DT');
    }, 500);
    
  • tefdattefdat Posts: 42Questions: 7Answers: 3

    Hi,
    @sparc64, your code example has one paranthesis on the end too much - was not able to get it run :)

    Following code worked for me:

                // Call datatables, and return the API to the variable for use in our code
                // Binds datatables to all elements with a class of datatable
                // https://stackoverflow.com/questions/5548893/jquery-datatables-delay-search-until-3-characters-been-typed-or-a-button-clicke/23897722#23897722
                var dtable = $("#example").dataTable().api();
                
                //https://datatables.net/reference/api/%24.fn.dataTable.util.throttle()
                var search =_.debounce(function(val) {
                    dtable.search( val ).draw();
                    },
                1000
                );
                
                // Grab the datatables input box and alter how it is bound to events
                $(".dataTables_filter input")
                    .unbind() // Unbind previous default bindings
                    .bind("keyup input", function(e) { // Bind our desired behavior
                        // If the length is 3 or more characters, or the user pressed ENTER, search
                        if(this.value.length >= 3 || e.keyCode == 13) {
                        //if(e.keyCode == 13) {
                            // Call the API search function
                            //dtable.search(this.value).draw();
                            search(this.value);
                        }
                        // Ensure we clear the search if they backspace far enough
                        if(this.value == "") {
                            //dtable.search("").draw();
                            search("");
                        }
                        return;
                    });
                
                });
    
This discussion has been closed.