Pivot grid performance

[nenadg]

Hi, I've been testing performance of Pivot grid (6.0.2) in rendering higher number of records. What I've got is this almost exponential drop in rendering speed when the number of records > 1000.

Notice that regular ExtJS grids that I've used were able to render up to 10k records in less than 500ms.
Attached graph shows the results of my tests. I'll try to publish the code snippets and update this post later ...

My question is - Could this be considered as bug?
ChartGo.png

[nenadg]

Update:

This chart shows regular `Ext.grid.Panel` with bufferedrenderer plugin. Notice that it renders ~200k records in the same time Pivot grid renders ~6k.

ChartGo (1).png

[ateodorescu]

You can't really compare the Grid with Pivot Grid.

The Pivot Grid is basically a Grid with additional calculations. Under the hood there is a Ext.pivot.matrix.* that does all calculations according to the dimensions configured on leftAxis, topAxis and aggregate. When the calculations are done a new store is generated which will be rendered by the Grid. The Bufferedrenderer plugin is enabled by default in Ext JS 6.

The performance of the Pivot Grid depends on:
- how many dimensions were provided for each axis and how many unique values exist for each dimension
- how many aggregate dimensions were defined

Hope this helps

[nenadg]

Hey Teodorescu,

I understand that the pivot calculations are expensive versus default Ext JS Grid which (in somewhat default scenario) has no any special calculations (excluding totaling), but I think that's not the problem here.

First, my case was pretty trivial (and you can take a look at it here).
Second, as trivial as the example is, it has:
- 1 column/topAxis (Year),
- 1 aggregate (value),
- 2 dimensions in leftAxis (Name, Company)

Take a look at these two images, first is memory profile during the test, and second is cpu profile:

memprof.jpgcpuprof.jpg

Maybe I'm wrong but processRecords ends with addItem which effectively adds item to DOM and this is super slow. It seems that problem is not in calculating values and creating the corresponding stores, but rather 'somewhere' in rendering.

[ateodorescu]

Hi nenadg,

As I can see in your SalesData if you have 2000 records in the store then you will have 2000 different persons. Each person will have 1 company which means that the pivot generated store will have 4001 records (2000 for persons - each person is a group and has subtotals, 2000 for companies and 1 for row grand total). Each pivot generated record will have 2 fields to aggregate for "value": 1 for the year 2016 and 1 for the column grand total. This means that we need to calculate the sum of value for 4001 * 2 = 8002 cells, even though there will be just one record per cell.

It takes time to parse 2000 records to build an additional 4001 records and then to calculate values for 8002 cells. After that the Grid receives a store with the calculated data to render it.

The pivot matrix doesn't add anything to the DOM. The processRecord/addItem function is used to identify unique values for each axis (pairs of person-company on left axis and years on top axis) and to build the collection of results (a result beeing in fact the value shown in a grid cell). After the pivot generated store is ready the grid will do operations in the DOM.

Try to generate in your SalesData just 10 unique persons for the entire data set and see the difference. You will end up with 1+ 10 * 2 = 21 records in the generated store and 21 * 2 = 42 cells to calculate.

That's why I said that the performance of the pivot grid depends on the dataset and its unique values for each dimension used on the axis.

Best,
Adrian

[nenadg]

Thanks, this is brief. what are the recommendations for organizing data to reduce calculation costs?

[ateodorescu]

Thanks, this is brief. what are the recommendations for organizing data to reduce calculation costs?

One solution would be to filter the dataset by timeline or smth so you can strip down the number of unique values for dimensions.
If there are lots of unique values on dimensions then limit the number of dimensions on axis.
Another solution would be to calculate remotely everything and pass the results via a Remote matrix.

[nenadg]

Thank you very much Adrian, I'll give this a try ...