Handsontable scrolling performance with lots of rows

Tags: #<Tag:0x00007f249ea97530>

Hi,

We are currently evaluating Handsontable. One of our requirements is to be able to show a table with a large number of rows. We will not be populating the data in all the rows, only a small subset.

This appears to work quite well, in the JSFiddle below we create a table with 100,000 rows and populate data in the first and last row:

https://jsfiddle.net/chrisvisokio/ex619yL3/

If you scroll the table you can see that the first and last rowsare populated, and you can scroll nicely to the bottom of the table.

However we need to be able to show more records, and this is where we encountered the problem. If you edit the fiddle and change the length to 500,000 rows (data.length = 500000), the table will still render, but it is very slow to scroll. So it appears as the number of the rowsin the table increases, the performance decreases. We would not expect this to occur if there is no data.

Profiling the performance indicated that there was a bottleneck in calculating row height (possibly in ViewportRowsCalculator) - although we did try passing in a fixed row height, this did not resolve the problem.

Any help you could provide here would be appreciated. Ideally we would like the table to perform nicely with 1M+ rows.

Regards

Chris

Hi @chris4

We are aware that handling such amount of data might be problematic performance wise. The solution that I can propose is to use pagination to divide the data into smaller chunks (it depends on data volume you have). However, there are some disadvantages. For example you won’t be able to use such features as column sorting on the whole table. It will work only in the current page. Here’s an one of the examples of how it can be done:

http://jsfiddle.net/aszymanski/7arygep2/

Hi Adrian,

Thanks for the reply. To be clear, we are not attempting to load a large amount of data. We simply want a table where we can show a large number of rows, with only a few rows populated with data. We can get this working relatively well with 100,000 rows, but as the number of rows increases the performance degrades significantly.

Also, pagination isn’t a great option for us. From profiling and looking at the code it appears that Handsontable should be able to render a large number of rows (eg. 1M+), since virtualisation means that it’s only rendering the visible rows, but there are a few places in the code that cause the performance issues (eg. the ViewportRowsCalculator). We were hoping for a solution where we could bypass/override this behaviour.

Hi Adrian, thanks for your quick reply. Jumping in here for my colleague Chris to help clarify this issue.

We’re currently evaluating HoT (a fantastic product, btw!) as the “engine” for our table editing in our data viz, analysis and editing product, where users commonly work with datasets well beyond 100,000 records. We’re aiming to create a seamless experience working with tables of large and small data, without resorting to pagination.

We are currently experimenting with creating a sparse array with (say) 1 million rows, but only populating data for the visible screen plus some buffer. This is great, and allows us to data-load only a small volume across the network, without any significant memory use in the JS runtime.

However, HoT performance degrades linearly with row count. Much beyond 100,000 it becomes a problem. Comparing with both online spreadsheets and Excel, 1 million rows seems like a reasonable expectation (and beyond is theoretically possible). But I can understand it may not be a limit you have strived for, due to memory and loading issues (which your typical user would need to develop a solution for, but we already have built in-house using a different table front-end).

I don’t understand why there is this degradation. If we have a fixed row height, calculating offsets or viewport sizes should be simple maths for HoT, and should not require iteration or anything that scales in complexity by number of rows. We’re wondering if there’s some way to tell HoT certain options that bypasses the iterative aspect.

@chris4, can you share the performance profile screenshot that shows the 1 million record case, expanded bottom-up? These guys might have some insight, just eyeballing it.

Note that sorting (for example) is not a concern for us, since we do sorting server-side as part of the data load.

Best,

Steve

image

@chris4 @steve1

Thank you guys for further explanation. I’m glad to hear to you decided to try our product. @chris4 suspicion that re-calculating rows and columns is the main issue here is true. It is fired on each scroll (that’s the designed behavior) and the greater the amount of rows or columns the more calculation are needed to be done.

The good news is that our developers are aware of that issue, and we already have some ideas how to solve it. Our roadmap is currently packed with other features but I hope that we will be able to address that problem in the nearest future. Here’s the reported issue I’ve mentioned:

Hi Adrian,

Thanks again for your prompt reply. It looks like our best option would be to wait for these fixes/improvements. Can you let us know if these are likely to be implemented in the next 6 months?

Regards

Chris

@chris4

I will raise this topic with our development team and I will try to find out what is the possible time frame for this issue. I should be able to let you know at the beginning of the next week.

1 Like

Hi @chris4 and @steve1! I am sure we can fine tune the performance for your challenging needs.

Have you seen the docs page https://handsontable.com/docs/performance/ ?

The number one tip on this page is to set the width for your columns. Applying this tip alone made the 500k configuration working for me: https://jsfiddle.net/warpech/p3s8j5bt/4/

I would be grateful for information how does that work for you.

Hi Marcin,

Thanks for the reply. Yes we do already set the column widths and also the row height. You are right that this does improve performance, however even in your example with 500k rows and column widths set, it is still noticeably slower to scroll, and the performance still degrades as the number of columns/rows increases. In the example given we only used 7 columns, but a typical use case for us would require us to support many more columns. As steve mentioned, it should be the case that the number of columns/rows does not effect performance, due to the virtualised nature of the table only rendering the visible grid.

You are right that this should not be a problem, because we render only the visible part of the grid.

With 100 columns, my JSFiddle still seems acceptable: https://jsfiddle.net/warpech/p3s8j5bt/5/, so I guess the problem arises with more complex configuration that you have.

I will be happy to help more, but then I need another more complex JSFiddle to investigate.

Marcin,

Even in this example i wouldn’t say the performance is acceptable. The further down you scroll it, the slower it gets. Try scrolling to the bottom.