SearchBuilder Crashes if you use equals condition on large data set

desperado

My data set has 5700 rows with 918 unique values in the column I am attempting to use the equals condition on.

This produces a very large drop down list, it would be better if equals just let you enter the text if a data set is over some limit.

With the 918 items I am able to select 1 and the filter works.

However once I select the add condition button the screen freezes and after a long wait the console has this error.

dataTables.searchBuilderFixedForButton.js:2875 Uncaught RangeError: Maximum call stack size exceeded
at Group._setCriteriaListeners (dataTables.searchBuilder.js:2875)
at Group.redrawContents (dataTables.searchBuilder.js:2545)
at HTMLDivElement.<anonymous> (dataTables.searchBuilder.js:3418)
at HTMLDivElement.dispatch (jquery-3.6.0.min.js:2)
at HTMLDivElement.v.handle (jquery-3.6.0.min.js:2)
at Object.trigger (jquery-3.6.0.min.js:2)
at HTMLDivElement.<anonymous> (jquery-3.6.0.min.js:2)
at Function.each (jquery-3.6.0.min.js:2)
at S.fn.init.each (jquery-3.6.0.min.js:2)
at S.fn.init.trigger (jquery-3.6.0.min.js:2)

NOTE: this is using search builder from nightly build to fix the broken Button issue in this thread. https://datatables.net/forums/discussion/69840/searchbuilder-v1-2-1-unclickable-button#latest

desperado

NOTE: searchBuilderFixedForButton.js is the name I used to denote the copy of searchBuilder.js I downloaded from the nightly build. I thought I renamed it to just "searchBuilder.js" in the stack trace I included but I forgot to rename on the first line..... Sorry about that, guess I should have just left the new name and explained it.

colin

We would need to see that, as it works as expected here - try the Name column which has 50k unique values. Please can you link to your page so we can debug,

Colin

burakt91

If you use Between filter on a numeric column and try clicking Add Condition again, it crashes or freezes. I submitted an issue on Github here https://github.com/DataTables/SearchBuilder/issues/4 I tried it with 50k data demo and it got frozen this time.

desperado

@colin Sorry for late reply but I gave up on search builder back in September because of other work taking priority and at the time search builder was new so had some issues. burakt91's post made be see your reply so I tried your test case.

I am back to using SearchBuilder and when I try the demo you provided the performance is terrible. It doesn't crash anymore but the page freezes up with a spinning wheel. I still think the list should NOT be built if the number of entires exceeds some number or there should be an option to set to make equals behave just like begins with letting you type your search criteria.

Here is a video demonstrating the 50K performance on my MacBook Pro 2019 running on a 2.4Ghz 8 Core Intel Core i9 with 32GB of Memory.

https://watch.screencastify.com/v/LbBTbPDoVQRhxeHKF6Mm

Just getting the list to display is tricky because it takes so long to build and then once you do getting to the bottom entires is way too difficult. The spinning wheel doesn't display when you first click equals so the user has no clue the list is being built. When equals does finally display and you click on the list the wheel start spinning and take almost 2 minutes to build the list. This freezes all browser tabs for that period and the user is likely to start clicking around which messes up the list display.

The first time I tried this I didn't know why the popup was displaying so I kept clicking and my browser was stuck displaying, dropping, displaying, dropping the list so many times I could no longer user my browser and I had to kill it. In the video I did my best to NOT click more than once but unless you move your focus into the list it drops just after displaying. Most users are not going to understand why that is happing and will keep clicking trying to get the list.

My use of SearchBuilder is performing OK at this point because my data is smaller then the 50K sample but I don't know where the tipping point lies so I do have concerns that this should be addressed in some way. I don't see the point in building a list once there are so many entries, nobody is going to scroll through so much data to pick one person.

sandy

Hi @desperado ,

I agree with you - no one is going to scroll through 50k rows to select one name! This example is there to highlight both the good and bad points of SearchBuilder performance with such a large dataset! That being said it isn't as bad for me - the select element is ready to dropdown after 10 seconds and displays the options in another 2 seconds.

When SearchBuilder operates with serverSide processing it replaces the select elements with input boxes. This is to improve performance. But I still think that the select element has its place when working on the client side. For example the office column - it doesn't have 50k distinct entries, so it loads and displays reasonably well!

There are a couple of ways to work around this performance issue...

1. Switch to using serverSide processing. Even if you were to swap the select element for an input, when you build more complex filters, the search still has to run on 50k distinct rows - that's never going to be fast on the client side! Switching to server side processing will give the users access quicker and filter the table faster as well.
2. Customise the conditions using searchBuilder.conditions[type][cond]. With SearchBuilder it is possible for you to change the conditions however you please. The documentation for this can be found here. So if you wanted to, you can switch to purely input elements by editing the conditions.
3. The other option, although not as nice as the above, is to restrict the columns that the user can filter using searchBuilder.columns. If there isn't really a use case for filtering on the distinct column this may be an option - but it won't be right for every case!

If anyone would find it useful, I'd be happy to put an example together showing how to replace all of the select elements with input elements as mentioned in point 2 - just let me know.

Thanks,
Sandy

desperado

@sandy Thanks, that makes sense. I wonder why my machine is so slow

One option would be to remove the equals from the options if the data is large.

From the documentation page you provided I see you can do that but I think it is all or nothing right? I don't know if I can do it for a single column only.

$('#example').DataTable({
    searchBuilder: {
        conditions: {
            num: {
                'equals': null
            }
        }
    }
})

Perhaps you can return null only for the column ?

I have the same issue with returning an input value. I think I would just do this.

$('#example').DataTable({
    searchBuilder:{
        conditions:{
            num:{
                'equals': {
                    init: function(that, fn, preDefined = null) {
                        // Declare the input element
                        let el = $('<input/>')
                            .addClass(that.classes.value)
                            .addClass(that.classes.input)
                            .on('input', function() { fn(that, this); });

                        // If there is a preDefined value then add it
                        if (preDefined !== null) {
                            $(el).val(preDefined[0]);
                        }

                        return el;
                    }
                }
            }
        }
    }
})

But then even the columns with only two possible values would result in the input field.

Perhaps in this case I could do something like this?

$('#example').DataTable({
    searchBuilder:{
        conditions:{
            num:{
                'equals': {
                    init: function(that, fn, preDefined = null) {
                        if (el.length > 300) {
                          // Large set so return input element 
                          // Declare the input element
                          let el = $('<input/>')
                              .addClass(that.classes.value)
                              .addClass(that.classes.input)
                              .on('input', function() { fn(that, this); });

                          // If there is a preDefined value then add it
                          if (preDefined !== null) {
                              $(el).val(preDefined[0]);
                          }

                          return el;
                        } else {
                          // Small enough set so return the selection input 
                          return [$(el[0]).children()[0].val()];
                        }
                    }
                }
            }
        }
    }
})

I think I am mixing init and inputValue here so it's wrong but I didn't see any example of how to return a selection input.

I am thinking an example would really be helpful if you don't mind building one. If you could trigger the switch from dropdown selection input to text input either based on the size of the data (ideal) or based on the column name (or index etc).

desperado

@sandy !!!!!! Update I almost have it working. !!!!!!!

Ironically my implementation of the selection is working but my implementation of the text entry field taken from the documentation page doesn't work.

I changed oninput to keyup so I could look for the enter key. I didn't want equals searching on every character entered. Other than that the code is copy/paste from the documentation.

Can you check and see if you know the issue?
https://jsfiddle.net/gouldner/7h6xLwno/

desperado

@sandy !!!!! Another update, I figured it out. !!!!!!

This is the fully working version and I moved the config into buttons to make sure that works also. Nice control over this! Cheers! I am all set now.

To anyone interested in seeing how this works. If you select "Name" in this example and "equals" the selection becomes a text entry (over 50 items), if you select Position it returns the selection list (under 50 items). Of course in my real code I will set the limit higher than 50.

https://jsfiddle.net/gouldner/jgo50e6c/

ctml

Brilliant, thanks for sharing desperado it helped me a lot! I am applying it to != as well as that also generates a select field.

ctml

One side effect is that returnSearch has no effect on this new input field and will search on key press regardless of this setting. This is another mechanism that also helps with performance of large tables.

https://datatables.net/extensions/searchbuilder/examples/initialisation/returnSearch.html

sandy

Hi @ctml ,

The example @desperado produced doesn't include the code for return search, which is fine because it is his custom condition. The original init function for input elements can be found here. You will be specifically interested in adding something like this to the function.

Hope this helps,
Sandy