Performance issue when handling multiple rendering in handsontable

Tags: #<Tag:0x00007f51c27b05a0> #<Tag:0x00007f51c0c57df8>
#1

I have used Handsontable, where I’m using API to fetch the data and populate the component with data and performing some calculations like formula parsing using HyperFormula, and some feature like hiding / showing column, freezing. But loading the table with 150 records itself makes the page lagging and I cannot do anything after that

Here is a sample code where I’m just passing the HotTable component for your reference.

<HotTable
data={data}
formulas={{
engine: HyperFormula,
}}
filters={true}
// enable the column menu
dropdownMenu={true}
height=“auto”
autoWrapRow={true}
autoWrapCol={true}
licenseKey=“non-commercial-and-evaluation”
/>

provide response asap as this is a critical issue and reduces user experience

#2

Hi @linthesh107

Thank you for contacting us. Such configuration with 150 rows shouldn’t render any performance issues. Please share a code demo where the issue can be replicated.

#3

Hi @adrian.szymanski
The above is the component which I have my data contains complex formula calculations like this

=AG7+AH7
=YEARFRAC(C7,TODAY())
=IF((Z7+AA7)>100,100,(Z7+AA7))

and also I’m fetching the data from backend and setting in a state and update the state and update the data accordingly

useEffect(() => {
const temp = data.filter();
const updatedData = testFunction(temp);
setData(updatedData);
},[selectedValue]);

This is major code part

#4

@adrian.szymanski, Please provide a solution, this limits the total performance of my application.

My total data contains 1000 records, when I change a category I’ll get 200 records for every category but for each dropdown change I find significant lag. And also I’m updating each cell and I’m having backend call for afterChange only when we get the response the page is response else it is producing some lag. The lag is reason my performance fails

#5

@adrian.szymanski

I guess everytime an update is made, entire table is created again instead of updating the same table

Performance down when more than 100 columns and 500 rows
Performance down when more than 100 columns and 500 rows
#6

@linthesh107

Please provide a code demo in a chosen sandbox (for example, StackBlitz) so I can check the problem.

#7

Hi @adrian.szymanski

I have a handsontable which bind with dynamic table. And the HOT has formulas on cells, Cell event , afterChange , events for data updation. if the table have more than 100 columns and 500 rows , gets hanged on mentioned events and table render.
its fine when the table is smaller.

const handleChange = async (changes: CellChange[] | null, source: ChangeSource) => {
const hotInstance = hotTableComponentRef?.current?.hotInstance;

if (source === "loadData") {
  return;
}

if (Array.isArray(changes)) {
  changes.forEach(async ([row, col, oldValue, newValue]) => {
    if (row !== undefined && col !== undefined && oldValue !== newValue) {
          const response = await backendCall(newValue);
       }
    }
  });
}

};

this is my afterChange hook where I’m updating a cell along with backend response.

      <HotTable
        ref={hotTableComponentRef}
        data={tableData}
        colHeaders={columnHeaders}
        columns={columnSettings}
        cells={(row) => {
          const cellProperties: CellProperties = {};

          data.forEach((data) => {
            if (condition) {
              cellProperties.readOnly = true;
              cellProperties.className = "classname";
            }
          });

          return cellProperties;
        }}
        formulas={{
          engine: HyperFormula,
        }}
        rowHeaders={true}
        width="100%"
        height={700}
        filters={true}
        dropdownMenu={["alignment", "---------", "filter_by_condition", "filter_by_value", "filter_action_bar"]}
        stretchH={"all"}
        contextMenu={[
          "alignment",
          "freeze_column",
          "unfreeze_column",
          "hidden_columns_hide",
          "hidden_columns_show",
        ]}
        hiddenColumns={{
          columns: columns,
          indicators: true,
        }}
        manualColumnFreeze={true}
        rowHeights={80}
        manualRowResize={true}
        autoWrapRow={true}
        autoWrapCol={true}
        undo={true}
        licenseKey={LICENSE_KEY}
        afterChange={handleChange}
        beforeColumnFreeze={(currentCol) => {
          tableIndex = currentCol; 
        }}
        afterColumnUnfreeze={(currentCol) => {
          if (tableIndex !== undefined) {
            const hotInstance = hotTableComponentRef?.current?.hotInstance;
            hotInstance.getPlugin("manualColumnMove").moveColumn(currentCol, tableIndex);
            hotInstance.render();
          }
        }}
      />

the above is my component. I’m having a dropdown based on the dropdown value a filter is applied to the tableData and passed everytime but it creates a performance lag even for the loading the data. and if the afterUpdate is called unless backend response is received the table gets stuck I cannot even scroll the table. This is only for data with 100 columns and 500 rows.

The lag in the table’s performance creates a bad user experience of this library. Can you kindly post any solution and help me out as this will create a significant importance.

Thanks in advance !!!

#8

The above code snippet is my major code, I’ll be using hyperformula to calculate values of the formula on the run, for example I have to compute 30 columns out of 50 columns using hyperformula, the formulas include excel cell reference. Kindly provide me a solution

#9

Hi @linthesh107

I’m still not able to use the code you sent to recreate the issue. In this case I can only provide the general guidance. Table with 100 columns and 500 rows shouldn’t generate any performance issues, even with the formulas but there are few improvements that can be done to optimize the performance.

For start I would recommend to check and implement operations batching: https://handsontable.com/docs/react-data-grid/batch-operations/ and general rules for performance optimization: https://handsontable.com/docs/react-data-grid/performance/ Then you can try to move all of the formulas calculations to the server-side so the don’t have impact on the table’s usability.

#10

Hi @adrian.szymanski, does the number of cells affect the performance of the table. Even for 1000 rows, consider having 100 columns so a total 100000 cells. Will it affect the performance drastically.

#11

@linthesh107

In cases where you have a large data set, it does. Especially if you use other plugins and perform the calculations on the front-end. The tips from our performance guide I linked in my previous post might be also useful in this case.