2

In my MVC 5 web application there are many instances in which users will require to view thousands of records within grids, now I managed to get around many performance related issues by utilising the following:

  • Lazy data loading.
  • JSON compression.
  • Progrssive Ajax Loading.
  • Virtual DOM
  • Only retreiving the most important columns.

Which all works well so far, the server hardly strugles to return and serialise data into JSON, my problem is at the client side, which is mainly:

  • As more data gets loaded into the grid more memory is consumed by the browser, for about 10,000 records it will consume around 1.5 GB.
  • Browser perfomance tends to degrarde for all open tabs while the performance of the working machine is normal and not degraded.

I am loading all records using progressive AJAX loading in the background and not by scrolling, the reason I am doing this is because users will complain about filters and sorting being applied to loaded records but not to all records.

I thought about server sorting and filtering instead but that would just is still not suitable for working with other data manipulation functionality such as grouping.

Is there no way I can still retireve this many records but avoid the two issues listed above?

The grid I am currently using is Tabulator

It would be nice to know how I could investigate what is taking up so much memory, is it data being loaded into the DOM or is it JS objects etc.

  • are you rendering all the rows at the same time or just one pages worth – Ewan Jul 21 '18 at 17:46
  • can you explain more about the ui, how is the user meant read that much data and why cant you show them just a sub section? – Ewan Jul 21 '18 at 17:48
  • 1
    You need a grid that discards the rows that are no longer being displayed. – Robert Harvey Jul 21 '18 at 17:49
  • @Ewan, no just about a page worth and a bit more to cover scrolling. – Abs Jul 22 '18 at 9:22
  • @RobertHarvey but isn't that what Virtual DOM does? Does that feature have a name I could look up or do you know of a such a grid to suggest? – Abs Jul 22 '18 at 9:24
2

The other posted answer is valid. Removing the hidden rows from the DOM will give you better performance. However, it doesn't address the underlying issue that you're passing all data to the browser before applying any filtering to it; which creates many tangential issues such as excessive bandwith usage and higher response times to get the list of items.

because users will complain about filters and sorting being applied to loaded records but not to all records

In other words, if you return page 2 (items 101 to 200), and the user has asked to filter on names containing "foo", you are applying that filter on those 100 items; instead of applying the filter on the database and then showing page 2 of the resulting set?

This is your issue. Filters must be applied before pagination. If you only perform the filtering on the webpage after all the items have loaded, then pagination must logically take place in the browser as well.

Workarounds for your current performance issue may exist (such as removing the filtered items from the DOM), but when the size of the data grows, the performance (and bandwidth) issue will pop up again in a different form (one that cannot be fixed by removing things from the DOM).
The only way to sustainably solves this is to not pass all the data to the webpage.

I thought about server sorting and filtering instead but that would just is still not suitable for working with other data manipulation functionality such as grouping.

I'm not following your explanation here. Grouping is just another step on the road to getting the output you want. There is a sequential logic about retrieving data:

  • Filter
  • Group
  • Order
  • Select
  • Paginate

Not all steps are necessary, but the order in which to execute them is usually the same (barring some fringe cases of e.g. complex group filtering - at which point grouping becomes a given and not an option as per your description)

What you're doing now is dumbing down your backend and taking on all the responsibilities in the frontend, which is simply not good design. The backend (especially a SQL-driven data storage) is tailored for efficient data retrieval.

I see no reasonable argument to shift all that responsibility to a frontend which is inherently less capable of handling large data collections and performing expensive data operations. It upsets every party involved:

  • The internal network between database and backend sends way more data than the user is actually interested in.
  • The backend needs to instantiate way more data than the user is actually interested in.
  • The external network between backend and frontend sends way more data than the user is actually interested in.
  • The frontend needs to handle way more data than the user is actually interested in.

You're making losses in network load, bandwidth usage, application performance and user experience, all for the benefit of not having to develop a method to filter/group/order/paginate data on the backend?

The pros are not outweighing the cons here.

[From a comment] there are client side functions that are driven by the users interaction that should take all data into account

You're putting the cart before the horse here. You're already calling them "client side function" which is the source of the issue. If you were to set up an interaction between the frontend and the backend so that the backend can handle the data manipulation needed, then they are no longer "client side functions".

I cannot think of any data manipulation that can only be done client-side and not server-side. I'm interested to hear an example of these "client side functions".

  • You are absolutely right, I guess I need to look into better design patterns to better distinguish what is done on the client and server end. You are right, my aim is to avoid recreating functionality that already exists on the client end but that is coming at a great expense. – Abs Jul 23 '18 at 7:23
  • Of your 5 steps, 4 of them are clauses in ansi SQL. Perhaps some day there will be a PAGE BY clause standardised. The "complex group filtering" is a HAVING clause? – Caleth Jul 23 '18 at 8:32
  • @Caleth "complex group filtering" can be what you need it to be, e.g. "group people by their first name and return me a list of the best paid people (one per unique first name". HAVING clauses fall under that too, but it's much broader than just that. I have no experience with ANSI, but in SQL Server you can use Row_Number() to paginate data. Not sure if ANSI implements the same functionality. – Flater Jul 23 '18 at 8:39
  • Oh, I know it can be implemented, but it would be so much neater to have a standardised "give me page 3 of 50 results per page", that was an independent clause, much like ORDER BY – Caleth Jul 23 '18 at 8:43
3

The best library I have used for viewing tons of data in a grid format is SlickGrid.

Here is a good example of a grid with 500,000 rows which demonstrates the performance.

Example: 500,000 rows in DataView

In a nutshell, once you get above a certain threshold (maybe 10,000 rows) then you can't afford to have every row in the grid rendered in the DOM. Every DOM element is going to comprise of multiple instances of objects and occupy some amount of memory.

Libraries such as SlickGrid get around this by only rendering the rows that are visible. Any rows not visible to the user are removed from the DOM. This leads to huge performance gains.

If you peruse the code then you'll get an idea of how this is accomplished. See https://github.com/mleibman/SlickGrid/blob/master/slick.grid.js#L1334 as an example.

  • Impressive, thanks I'll take a look. Currently I am using tabulator.info – Abs Jul 22 '18 at 9:29
  • SlikGrid works nicelly by the way, it lives up to it's name but it's hardly feature rich. – Abs Jul 24 '18 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.