Most Efficient way of Filtering an Html Table?

Question

I have an ajax function which call a servlet to get list of products from various webservices, the number of products can go upto 100,000. i need to show this list in a html table.

I am trying to provide users an interface to filter this list based on several criteria. Currently i am using a simple jquery plugin to achieve this, but i found it to hog memory and time. the javascript that i use basically uses regex to search and filter rows matching the filtering criteria.

I was thinking of an alternate solution wherein i filter the json array returned by my servlet and bind the html table to it. Is there a way to achieve this, if there is, then is it more efficient than the regex approach.

Answer 1

1. User enters filter and hits search.
2. Ajax call to database, database has indexes on appropriate columns and the database does the filtering.
3. Database returns result
4. Show result in table. (Probably want it to be paged to only show 100-1000 rows at a time because 100,000 rows in a table can really slow down your browser.

Edit: Since you don't have a database, the best you're going to be able to do is run the regex over the JSON dataset and add results that match to the table. You'll want to save the JSON dataset in a variable in case they change the search. (I'm assuming that right now you're adding everything to the table and then using the jquery table plugin to filter it)

Answer 2

If you're getting back xml, you could just use jQuery selection

$('.class', context) where context is your xml response.

From this selection, you could just write the xml to the page and use CSS to style it. That's where I'd start at first, at least. I'm doing something similar in one of my applications, but my dataset is smaller.

Answer 3

I don't know what you mean by "bind"? You can parse JSON and then use for loop (or $.each()) to populate ether straight HTML or by using grid plugin's insert/add

Answer 4

I'm assuming that by filtering you mean only displaying a subset of the data; and not sorting.

As you are populating the data into the table add classes to each row for everything in that row you want to filter by. e.g.:

<tr class="filter1 filter2 filter3">....
<tr class="filter1 filter3">....
<tr class="filter2">....
<tr class="filter3">....

Then when you want to apply a filter you can do something like:

$('TR:not(.filter1)').hide();

Answer 5

Going through up to 100,000 items and checking if they meet your criteria is going to take a while, especially if the criteria might be complex (must be CONDO with 2 OR 3 bedrooms NOT in zip code 12345 and FIREPLACE but not JACUZZI).

Perhaps your servlet could cache the data for the 100,000 items and it could do the filtering, based on criteria posted by the user's browser. It could return, say, "items 1-50 of 12,456 selected from 100,000" and let the user page forward to the next 50 or so, and even select how many items to get back (25, 50, all).

If they select "all" before narrowing down the number very far, then a halfway observant user will expect it to take a while to load.

In other words, don't even TRY to manage the 100,000 items in the browser, let the server do it.

Answer 6

I agree with Berry that 100000 rows in the browser is bit of a stretch, but if there's anything that comes close to handling something of that magnitude then it's jOrder. http://github.com/danstocker/jorder

Create a jOrder table based on your JSON, and add the most necessary indexes. I mean the ones that you must at all cost filter by.

E.g. you have a "Name" field with people's names.

var table = jOrder(json)
    .index('name', ['Name'], { sorted: true, ordered: true });

Then, for instance, this is how you select the records where the Name field starts with "John":

var filtered = table.where([{ Name: 'John' }], { mode: jOrder.startof, renumber: true });

Later, if you need paging in your table, just feed the table builder a filtered.slice(...).