[XGrid] Performance issue when sorting/filtering 100k rows client-side

[arheinjohnson]

• The issue is present in the latest release.
• I have searched the issues of this repository and believe that this is not a duplicate.

Current Behavior 😯

When we use XGrid for 100k rows, performance is very slow and usually crashes the browser. For 50k rows, performance is still unacceptably slow. How can we improve the performance? What are the trade offs of server side filtering and server side paginating?

Expected Behavior 🤔

Faster UX.

Steps to Reproduce 🕹

https://codesandbox.io/s/awesome-framework-kqbcp?file=/src/Demo.tsx

Your Environment 🌎

`npx @material-ui/envinfo`

- Browser:
    Chrome
- System:
    OS: Windows 10 10.0.18363
- Binaries:
    Node: 14.16.0 - C:\Program Files\nodejs\node.EXE
    Yarn: Not Found
    npm: 6.14.11 - C:\Program Files\nodejs\npm.CMD
- Browsers:
    Chrome: 90.0.4430.85
    Edge: Spartan (44.18362.1474.0)

Order id 💳

Order #23360

[oliviertassinari]

@arheinjohnson Do you have a reproduction we can look into? Also, could you be more specific on what you mean by slow (for what use case/action)?

[arheinjohnson]

Right, so scrolling and pagination are still great, but the sort and filter experience becomes painful.

I was surprised to see this, because the Commercial version demo here is rather fast:
https://material-ui.com/components/data-grid/#commercial-version

Do you recommend against server side pagination and filtering?

Here's a reproduction, but it has 1mil rows to get my point across, since there are only two columns.
https://codesandbox.io/s/awesome-framework-kqbcp?file=/src/Demo.tsx

[oliviertassinari]

@arheinjohnson I'm confused. The reproduction is using client-side sorting and filtering but you seem to use the server-side in real-life.

[arheinjohnson]

Oh my mistake for not being clear, I am using client-side and am asking how to improve performance. For example, would you recommend server-side if I have data sets > 100k rows? Or am I missing something else obvious about XGrid to make it perform faster?

[oliviertassinari]

I have updated the reproduction to log the time a sort and filter would normally take: https://codesandbox.io/s/great-rain-uy0be?file=/src/Demo.tsx. You are right, it should be really fast: https://codesandbox.io/s/great-rain-uy0be?file=/src/Demo.tsx.

I can confirm that there is something significantly wrong. Sorting 100k rows in the live demo should take at most 200ms not ~2,000ms.

[arheinjohnson]

What do you recommend we do in the short term, or will a resolution be available sooner than later?

[oliviertassinari]

@dtassone was planning to work on performance issues this sprint.

[dtassone]

I have removed the heavy work of the params that we send in events.

[arheinjohnson]

Sorry if this is obvious, do we need to upgrade XGrid to pull the performance improvements?

[oliviertassinari]

@arheinjohnson check the CI status of #1513, you should find a codesandbox step. Open it. Then, you should find a version of the component you can install with the WIP changes.

[oliviertassinari]

https://github.com/mui-org/material-ui/blob/next/CONTRIBUTING.md#how-can-i-use-a-change-that-wasnt-released-yet

[dtassone]

I believe there is clear perf improvement
#1513 (comment)

[arheinjohnson]

Thanks for this! Weird, when I went to sort "Commodity" ASC, I received an unresponsive page error. Due to firewall, I'm unable to upload a screenshot.

[arheinjohnson]

So, how much faster should server-side filtering and server-side pagination be for XGrid? Wondering if you can help me understand the trade-offs for server-side versus client-side.

[oliviertassinari]

@arheinjohnson It would depend.

Cost of client-side filtering:

1. Loading all the data, slow, I doubt you can load more than 10k records, unless spending a couple of seconds.
2. Filtering client-side, almost instant, 10ms max.

Cost of server-side filtering:

1. The network request ~50ms, each time
2. Run the query on the database ~100ms?
3. Overhead, 100ms?

[arheinjohnson]

That helps, thanks @oliviertassinari. We'll try out server-side filtering and server-side pagination to see if we get our desired performance. Some of the database queries can be cut out with caching. Is there any hope for very fast client-side loading and filtering in the future for > 100k records?

[oliviertassinari]

Is there any hope for very fast client-side loading and filtering in the future for > 100k records?

For 100k records, I don't see why it would take more than 1ms, 10ms top, client-side, with proper caching.
For >100k records, we might start to hit other issues: #240.

[arheinjohnson]

Feels like I'm going in a circle a bit, sorry for that. Well we upgraded from 4.0.0-alpha.24 up to 4.0.0-alpha.27 and it seems to have alleviated our XGrid performance problems for 100k rows for now. Sorting and filtering client-side are pretty fast. Will let you know as we learn more! Thank you for the component!

[oliviertassinari]

@arheinjohnson Actually, I'm curious. How did you manage to load 100k records client-side? I had always assumed that this wasn't really possible in production, that 100k row cases would be server-side unless or when running inside an electron environment with direct fs access.

Stay posted, we will close this issue once we are happy with the result. We will share progress along the way.

[arheinjohnson]

I am not sure what you are asking, I'm using a standard http request to retrieve rows from an API. By the way I am using client-side pagination, not loading 100k rows into a single page.

[dtassone]

To load large amount of data on the client side, you should batch server updates with a stream, and update the grid using
apiRef.current.updateRows(<batch here>)

Also performance will improve when we merge #1513

[zig145]

I want to jump in to point out that there is major performance issue with filtering since 4.0.0-alpha.23. You can see it here https://material-ui.com/components/data-grid/#commercial-version with filter "commodity contains 'soy'". That is taking more than 1m to filter. If you open up the code sandbox for this example (https://codesandbox.io/s/cgbej), and switch to 4.0.0-alpha.22, you'll see that filter performs in milliseconds speed rather than minutes. It doesn't look to me like #1513 addresses this, but I could be wrong.

edit:
I take that last bit back, performance of filter does look much better here. https://deploy-preview-1513--material-ui-x.netlify.app/components/data-grid/#commercial-version

[oliviertassinari]

@dtassone Should we close this issue? It seems that we have fixed the main pain point (slow filtering).