Representing a table for dynamic filtering

Question

I need to represent a table containing data about some tests and their results.

There are over 100K tests and several results per test.
It needs to show the results of tests made for a device.
One idea is to retrieve data from the server and represent it using jqGrid.

--------------------------
device                   |
--------------------------
     | version1 |version2|
testA| failed   | Passed |
testB|...       | ...    |
.                        |
.                        |
.                        |
-------------------------|

Which of the following methods would be the best way to dynamically retrieve and represent the data and why? :

Retrieve all data from the server and filter it using jquery and javascript.
Retrieve filtered data from server and just represent it.

You should understand that sorting and filtering of the data in JavaScript is much slowly as in the native code of SQL-based database. So the server based sorting, paging and filtering of data are better in case of large dataset. Especially carefully you should consider different ways of the data filtering or another ways of displaying results like charts, pipes and so on. Just to show thousand (or even hundred thousand) of rows of the data have **no sense**. You should give the user tools **to analyse** (to filter for example) the tests. — Oleg, May 04 '11 at 18:28
If you use ASP.NET on the server side you can find the example of the server code implemented the server side sorting, paging and filtering [here](http://stackoverflow.com/questions/5500805/asp-net-mvc-2-0-implementation-of-searching-in-jqgrid/5501644#5501644). The answer include Visual Studio Project which you can download. — Oleg, May 04 '11 at 19:43

score 0 · Answer 1 · answered May 04 '11 at 17:12

Always get the filtered data:

fewer rows processed by database (if indexed properly) means that the db will scale better.
less data being sent across the network means that it will scale better
less data being parsed on the client side will increase perceived performance
less data on the client side will reduce the memory footprint

And if your grid is doing paging, you'll only want to retrieve a pagefull of records at a time, too.

Good luck.

score 0 · Answer 2 · answered May 04 '11 at 17:16

I think 100K records is a lot to transfer and probably also could be too much for the browser to handle.
Think of the KBs that the browser will have to get as an HTML.
I have no information about the kind of filters that you are needing to add into this page, but based on this 100.000 records total size I'm pretty sure you should filter and paginate on server-side. It is barely possible that the user will be willing to check on that many records at the same time.

Anyway I suggest you to check the cost of network usage and the available processing capacity in server and client side to decide it.
However there is pretty much written in internet about this criterias.

score 0 · Accepted Answer · edited May 23 '17 at 12:03

Answering this isn't quite as black/white as you want. 100k+ records is a large dataset, regardless of the tool used to display it (...and the human's ability to usefully understand it). Can the users deal with it paginated (on the client or server), or do they need the data displayed all at once? How and when does the data need to be filtered to make this tool useful to the user? Some considerations:

Javascript is robust, but lengthy DOM update to display the large set simply will tax some older browsers (especially IE6) and cause them to lock up. You should know your userbase -- if they are mostly on newer browsers then this won't be as much an issue.
jqGrid has a demo with 12k records (Advanced>>Big Sets). Notice they are not loading the whole thing.
Minimize DOM updates. Filter your data in memory, then write once.
UI that includes "loading" animation, DOM-blocking, button-disabling, etc will help stop duplicate server/filter requests from impatient users that just might bring down this web app (or their own browser) when trying to use the dataset.
Minimize the amount of javascript used to style the data (alternate row classes, row highlights on rollover, that sort of thing). Rely on CSS or disable some of these features in the jQuery plugin. This will help with display speed.

Representing a table for dynamic filtering

3 Answers3