[data grid] Pivoting/aggregation performance (+allow sort-dependent aggregations)

[lauri865]

• [x] I have followed (at least) the PR section of the contributing guide.

Fixes #18342 (the first part of it)

Adds a new applySorting?: boolean property to GridAggregationFunction

The only thing that probably needs additional polish is this:

useGridEvent(apiRef, 'filteredRowsSet', applyAggregation);
useGridEvent(apiRef, 'sortedRowsSet', applyAggregation);

These can in some instances (on mount) be triggered sequentially if I remember correctly (correct me if I'm wrong), so might be some room for optimisation.

Changes:

• Optimized columns updates (-800ms with a large amount of columns)
• Optimized column group creation (50-100x improvement under large number of columns)
• Improved aggregation performance (from 6000ms+ -> 275ms in my test case)
• Removed pivotModeChange control state, because pivotMode is not strictly controlled, and in an uncontrolled mode, it delays updates by 1 render
• Fixed a case whereby rows are improperly restored (props.rows takes overrides any subsequent changes made via the apiRef)
• Created windowed and chunked aggregation logic, which calculates the current viewport instantly, and the rest in chunks to avoid locking the main thread. Chunksize logic could be improved further; one idea is to continue with the chunks until we hit X amount of ms, or recalculate chunkSize based on how long the previous chunk took.
• Deduplicated calls to applyAggregation (currently it was called many times in a row)
• Support for applySorting in aggregation functions
• Expose field to getCellValue in aggregation functions to make custom logic possible, especially in the context of pivoting where fields are automatically generated
• Fixed: column groups based on numbers were not sorted correctly, as all headernames were coerced to strings
• Fixed: pivoting doesn't initialize when rows are updated with setRows while isLoading internal state is still true. Common pattern would be to set rows within useLayoutEffect when isLoading is changing (to avoid flickering), and that can't be caught by the current logic since an upperlevel useLayoutEffect will always run before the update of the internal state. Deferred initializing nonPivotDataRef to the first row update (works with all of setRows, updateRows and props.rows) instead of listening to the isLoading change of internal state.

Performance updates; before:

https://github.com/user-attachments/assets/34bd24b8-7f26-467e-83a9-9478e320adfc

After:

https://github.com/user-attachments/assets/d6985bdb-b078-4f84-99b3-913cbd85b9bd

[mui-bot]

Deploy preview: https://deploy-preview-18348--material-ui-x.netlify.app/

Bundle size report

Total Size Change: 🔺+10.5KB^(+0.08%) - Total Gzip Change: 🔺+3.64KB^(+0.09%) Files: 122 total (0 added, 0 removed, 6 changed)

Show details for 100 more bundles (22 more not shown)

@mui/x-data-grid-premium parsed: 🔺+3.25KB^(+0.57%) gzip: 🔺+1.07KB^(+0.66%) @mui/x-data-grid-premium/DataGridPremium parsed: 🔺+3.21KB^(+0.61%) gzip: 🔺+1.2KB^(+0.79%) @mui/x-data-grid parsed: 🔺+1.17KB^(+0.31%) gzip: 🔺+399B^(+0.36%) @mui/x-data-grid-pro parsed: 🔺+1.17KB^(+0.26%) gzip: 🔺+383B^(+0.29%) @mui/x-data-grid-pro/DataGridPro parsed: 🔺+855B^(+0.19%) gzip: 🔺+290B^(+0.23%) @mui/x-data-grid/DataGrid parsed: 🔺+855B^(+0.23%) gzip: 🔺+295B^(+0.28%) @mui/x-charts parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/BarChartPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/ChartContainerPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/ChartDataProviderPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/ChartsToolbarPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/ChartZoomSlider parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/FunnelChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/Heatmap parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/LineChartPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/PieChartPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/RadarChartPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts-pro/ScatterChartPro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/BarChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartContainer parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartDataProvider parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsAxis parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsAxisHighlight parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsClipPath parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsGrid parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsLabel parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsLegend parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsLocalizationProvider parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsOverlay parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsReferenceLine parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsSurface parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsText parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsTooltip parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsXAxis parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ChartsYAxis parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/Gauge parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/LineChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/PieChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/RadarChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/ScatterChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/SparkLineChart parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-charts/Toolbar parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterDateFns parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterDateFnsJalali parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterDayjs parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterLuxon parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterMoment parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterMomentHijri parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/AdapterMomentJalaali parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DateRangeCalendar parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DateRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DateRangePickerDay parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DateRangePickerDay2 parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DateTimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DesktopDateRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DesktopDateTimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/DesktopTimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/LocalizationProvider parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MobileDateRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MobileDateTimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MobileTimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MultiInputDateRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MultiInputDateTimeRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/MultiInputTimeRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/PickersRangeCalendarHeader parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/SingleInputDateRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/SingleInputDateTimeRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/SingleInputTimeRangeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/StaticDateRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers-pro/TimeRangePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterDateFns parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterDateFnsBase parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterDateFnsJalali parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterDayjs parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterLuxon parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterMoment parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterMomentHijri parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/AdapterMomentJalaali parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DateCalendar parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DateField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DatePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DateTimeField parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DateTimePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DayCalendarSkeleton parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DesktopDatePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DesktopDateTimePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DesktopTimePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/DigitalClock parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/LocalizationProvider parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/MobileDatePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/MobileDateTimePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/MobileTimePicker parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/MonthCalendar parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/MultiSectionDigitalClock parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/PickerDay2 parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/PickersActionBar parsed: 0B^(0.00%) gzip: 0B^(0.00%) @mui/x-date-pickers/PickersCalendarHeader parsed: 0B^(0.00%) gzip: 0B^(0.00%)

Details of bundle changes

Generated by :no_entry_sign: dangerJS against de6d7b09e7d8f40845e5319cc596577218e960dc

[lauri865]

Now also exposed field to getCellValue that's required to pull the correct data for pivoting, fixing the second part of the issue. I also exposed api in aggregationFunction.apply, as I've felt many times that I could build more flexible and performant aggregations if only I had more context to go by.

[lauri865]

Enabling pivot seems to trigger 5 instances of "filteredRowsSet" (each of them retriggers aggregations). And the same for "sortedRowsSet for whatever reason @cherniavskii

Adding applySorting to the mix causes a visible slowdown.

Just press the toggle button and check console: https://stackblitz.com/edit/g4dsphrl-sxhvdgqz?file=src%2FDemo.tsx,package.json

[lauri865]

I tried deduplicating some more expensive calls, but probably broke some tests (not sure if for functionality, but at least it will complain about act().

One thing I still don't understand though – when stopping pivoting, columns get rehydrated 6 times: 6x MUI X: useGridColumns - Columns pipe processing have changed, regenerating the columns

[lauri865]

Did a bunch of optimisations in aggregation calculations. For a a large pivot table (10k rows, pivoted by date (value) and commodity in the dataset, summing "total in usd") – computation time went down from 6000ms+ to 375ms. A big culprit was nested function calls to access row values, which we can skip for pivoting + doing another full recursion over all rows/columns. isNumber custom function wrapper also added quite significant overhead for summing.

Can be still polished a bit I guess.

[lauri865]

From 375ms -> 260ms in my test case now that I stoped storing undefined values. I guess it's fair to assume that undefined has no meaning in aggreations, nulls might (although even that is slightly questionable, and could be optionally toggled). And there's tons of undefined values in pivoting at least.

[lauri865]

Fixed the last issue as well with cascading hydrateColumns updates by re-registering the pipeApplier when pivotMode changes. So the slow restore after pivoting in the after video should also hopefully be fixed now.

[lauri865]

Generalized the aggregation now for any depth. For traversal, we only fetch direct leaf values, otherwise merge the precomputed values on group level, instead of going through the nested tree over and over again.

[lauri865]

This took about 800ms when toggling a pivot with a large list of columns, now down to <1ms: https://github.com/mui/mui-x/blob/8d434ffcb9d091293a682d79be94a81a9aa66812/packages/x-data-grid/src/hooks/features/columns/gridColumnsUtils.ts#L347-L353

There's still some room to optimise – 168ms just for columns + aggregation + row grouping – easy to lock up the main thread still:

• useGridColumns createColumnsState: 30.65087890625 ms
• useGridColumns setGridColumnsState: 138.465087890625 ms

[lauri865]

Toggling off pivoting doesn't lock up the UI anymore:

https://github.com/user-attachments/assets/e31021ba-b56f-4501-abe3-4ea9c173fec3

[lauri865]

Further improved column grouping performance by 50x+, which in my test case locked up the main thread, now fits into a single frame. It was also previously run twice in a row when enabling pivoting.

[lauri865]

Implemented viewport aware, chunked aggregations to avoid locking. Switch animation runs buttery smooth even with 10k rows. With 100k rows it's still usable, but there's a slight lockup, depending on the dimensionality, but the bottleneck is not in aggregation anymore.

[lauri865]

New video added to the opening post ;)

[lauri865]

I'm not sure how to satisfy the tests without a lot of work. The quemicrotask and settimeout makes the test fail. Don't feel particularly like (=dont have the time for) updating all the aggregation and pivoting tests with mock timers.

[lauri865]

There's one last small thing I'd want to add - default sort order for columns. I'm yet to come across a situation where I'd like the (top level of) pivoted columns to appear in order they show up in the data. Especially not numerical categories or dates, but also imagine a list of countries showing up in a random order. So I always have to manually sort the columns anyways when changing them.

There's a few solutions:

1. Apply default sort ascending for columns added via the UI
2. The first, but only applying for the first level (although then we'd need to adjust when reordering)
3. The first, but only for number and date types
4. Globally inject a default, but then we'd need to add a null value to the interface to support unsorted views as well.

Thoughts @cherniavskii?

[lauri865]

Fixed another issue that was bugging us that prevented pivoting from initializing when setting rows with apiRef.current.setRows while isLoading is changing from true -> false.

Also added a path to re-run state initializers to avoid cascading and conflicting states (e.g. changing rowGroupingModel ahead of setting rows would recalculate row groups on the old data, and the same for aggregation). That made brough a 100k row pivot to quite acceptable performance levels.

Note though: controlled mode is still quite unoptimized. It calculates the pivoting state, throws it away (ignores controlled state being set), and recalculates it once the upper-level state/prop updates (cc @cherniavskii).

100k rows:

https://github.com/user-attachments/assets/f5d739b5-0429-428d-a35b-275ab1e0d4d6

[lauri865]

Also implemented exportState with pivoting (we really needed it, since otherwise restored state is messed up).

[github-actions[bot]]

This pull request has conflicts, please resolve those before we can evaluate the pull request.

[github-actions[bot]]

This pull request has conflicts, please resolve those before we can evaluate the pull request.

[github-actions[bot]]

This pull request has conflicts, please resolve those before we can evaluate the pull request.

[lauri865]

Thanks for all the heavy lifting @cherniavskii for making this PR happen – and sorry for all the rework for tests this brought 🙏❤️

[cherniavskii]

@lauri865 Thank you for making it possible, we appreciate it!