Migration from 13.1.0 to 15.1.0 reduces performance (API core getData degraded?)

[mr.fursy]

Hello devs and supporters. In our project we needed to update to the latest version of the library because we have a bug related to the inability to copy from locked cells. This bug was fixed in 14.*
(Copying values from locked cells does not work), but in the major version 14 of the library a regression occurred
(Migration from 13.1.0 to 14.6.1 breaks table when trying to paste values into columns with locked cells) related to fast editing. That’s why I waited for the release of version 15, where this issue has already been resolved.

Now I have encountered a severe performance drop. When initializing the table, the following block of code is executed:

useEffect(() => {
  if (
    vaultLoadDataStatus === 'IN_PROCESS' ||
    inputValues.length === 0 ||
    hot === null
  )
    return
  console.time('updateInputResultProps')
  updateInputResultProps(hot)
  console.timeEnd('updateInputResultProps')
}, [vaultLoadDataStatus, inputValues, plantsListForAside, hot])

In library version 13.1.0 the function updateInputResultProps executes in about ~230 ms, while in library version 15.1.0 it takes about ~6700 ms—a slowdown by a factor of 30!

Below is the implementation of the function:

updateInputResultProps = (hot: Handsontable) => {
  this.vaultSpreadsheet.inputResultProps =
    this.vaultSpreadsheet.colNumberPerStation.map((stationColNum, idx) => {
      const sumIdx = [...this.inputResultCellIndexes].splice(idx * 3, idx * 3 + 3)
      const plantFullData = this.plantsData.find(
        (el) => el.plantId === this._displayedPlants[idx]?.plantId
      )
      const viewOnly = this._displayedPlants[idx]?.viewOnly ?? false
      const FLOOD_MODE_WATCH =
        this.floodsLeft?.some(
          (id) => id === this._displayedPlants[idx]?.plantId
        ) ?? false
      const valueMin = FLOOD_MODE_WATCH
        ? `=${calcCellFromAlphabet(sumIdx[0] + 1)}25`
        : this.inputValues[idx]?.W_MIN
      const valueMax = FLOOD_MODE_WATCH
        ? `=${calcCellFromAlphabet(sumIdx[2] + 1)}25`
        : this.inputValues[idx]?.W_MAX
      const valueGen = FLOOD_MODE_WATCH
        ? `=${calcCellFromAlphabet(sumIdx[2] + 1)}25`
        : this.inputValues[idx]?.P_GEN_TARGET
      const isWMin = this.inputValues[idx]
        ? Object.keys(this.inputValues[idx])?.some((el) => el === 'W_MIN')
        : false
      const isWMax = this.inputValues[idx]
        ? Object.keys(this.inputValues[idx])?.some((el) => el === 'W_MAX')
        : false
      const isPGen = this.inputValues[idx]
        ? Object.keys(this.inputValues[idx])?.some(
            (el) => el === 'P_GEN_TARGET'
          )
        : false
      const inputValuesRes: Omit<
        IVaultStore['inputValues'][0],
        'plantId'
      > = {
        W_MIN: isWMin ? valueMin : undefined,
        W_MAX: isWMax ? valueMax : undefined,
        P_GEN_TARGET: isPGen ? valueGen : undefined,
      }
      if (FLOOD_MODE_WATCH) {
        console.time('FLOOD_MODE_WATCH')
        if (plantFullData?.plantOptimized) {
          inputValuesRes['W_MIN'] = hot.getData()[24][sumIdx[0]]
          inputValuesRes['W_MAX'] = hot.getData()[24][sumIdx[2]]
        } else {
          inputValuesRes['P_GEN_TARGET'] = hot.getData()[24][sumIdx[2]]
        }
        console.timeEnd('FLOOD_MODE_WATCH')
      }

      console.time('validatePGenTarget')
      const validate_P_GEN = validatePGenTarget(
        hot,
        inputValuesRes,
        {
          pMin: sumIdx[0],
          pGen: sumIdx[1],
          pMax: sumIdx[2],
        },
        isPGen
      )
      console.timeEnd('validatePGenTarget')

      console.time('validateWMin')
      const validate_W_MIN = validateWMin(
        hot,
        inputValuesRes,
        {
          pMin: sumIdx[0],
          pGen: sumIdx[1],
          pMax: sumIdx[2],
        },
        isWMin
      )
      console.timeEnd('validateWMin')

      console.time('validateWMax')
      const validate_W_MAX = validateWMax(
        hot,
        inputValuesRes,
        {
          pMin: sumIdx[0],
          pGen: sumIdx[1],
          pMax: sumIdx[2],
        },
        isWMax
      )
      console.timeEnd('validateWMax')
      const isErrorPGENTARGET = typeof validate_P_GEN !== 'string'
      const isErrorWMIN = typeof validate_W_MIN !== 'string'
      const isErrorWMAX = typeof validate_W_MAX !== 'string'
      return {
        stationColNum: stationColNum || 0,
        wMin: {
          active: isWMin,
          disabled:
            this._displayedPlants[idx]?.accepted ||
            viewOnly ||
            this._isLastDay ||
            FLOOD_MODE_WATCH ||
            plantFullData?.parameters?.E_MAX_E_MIN?.value.turnedOn ||
            !this._editMode ||
            this._isFinishStage,
          value: valueMin,
          isValid: isErrorWMIN,
          comment:
            validate_W_MIN !== undefined
              ? getStyledComment(validate_W_MIN)
              : undefined,
        },
        pGen: {
          active: isPGen,
          disabled:
            this._displayedPlants[idx]?.accepted ||
            viewOnly ||
            this._isLastDay ||
            FLOOD_MODE_WATCH ||
            !this._editMode ||
            this._isFinishStage,
          value: valueGen,
          isValid: isErrorPGENTARGET,
          comment:
            validate_P_GEN !== undefined
              ? getStyledComment(validate_P_GEN)
              : undefined,
        },
        wMax: {
          active: isWMax,
          disabled:
            this._displayedPlants[idx]?.accepted ||
            viewOnly ||
            this._isLastDay ||
            FLOOD_MODE_WATCH ||
            !this._editMode ||
            this._isFinishStage,
          value: valueMax,
          isValid: isErrorWMAX,
          comment:
            validate_W_MAX !== undefined
              ? getStyledComment(validate_W_MAX)
              : undefined,
        },
      }
    })
}

I tried to localize the problem and find the bottleneck that caused the slowdown. Apparently, the delay is linked to retrieving data from the table by calling the table instance’s API (hotUp.getData()), which is present in all of the validator functions.

For example:

const validateWMin = (
  hotUp: Handsontable | null,
  inputValues: Omit<IVaultStore['inputValues'][0], 'plantId'>,
  sumIdx: IValidateSumIdx & { pGen: number },
  active: boolean
) => {
  console.time('tableDataUp')
  const tableDataUp = hotUp?.getData()?.slice(24, 25)[0] ?? []
  console.timeEnd('tableDataUp')
  // other lines of code
}

const validateWMax = (
  hotUp: Handsontable | null,
  inputValues: Omit<IVaultStore['inputValues'][0], 'plantId'>,
  sumIdx: IValidateSumIdx & { pGen: number },
  active: boolean
) => {
  console.time('validateWMax_tableDataUp')
  const tableDataUp = hotUp?.getData()?.slice(24, 25)[0] ?? []
  console.timeEnd('validateWMax_tableDataUp')
  // other lines of code
}

const validatePGenTarget = (
  hotUp: Handsontable | null,
  inputValues: Omit<IVaultStore['inputValues'][0], 'plantId'>,
  sumIdx: IValidateSumIdx & { pGen: number },
  active: boolean
) => {
  console.time('validatePGenTarget_tableDataUp')
  const tableDataUp = hotUp?.getData()?.slice(24, 25)[0] ?? []
  console.timeEnd('validatePGenTarget_tableDataUp')
  // other lines of code
}

I wrapped each line

const tableDataUp = hotUp?.getData()?.slice(24, 25)[0] ?? []

in a timer in the validator functions and observed the following behavior.

13.1.0

15.1.0

Slowing down the execution of each hotUp?.getData() call by almost 30 times

What is causing such a slowdown of the getData method? How can this problem be solved?

[mr.fursy]

I made some optimizations. Instead of using
const tableDataUp = hotUp?.getData()?.slice(24, 25)[0] ?? []
in each validator function, I retrieve the data once at the updateInputResultProps
( const rowData = hot.getDataAtRow(24) ?? [] )
and pass it down to the validator functions. With this change, I was able to reduce the total execution time of updateInputResultProps to <40ms, which is even faster than the old implementation on version 13.1.0.

15.1.0

On 13.1.0 even faster

However, even with these optimizations, the first table render is still slower (~7s) compared to the old validation implementation + version 13.1.0 (~4s). It feels like the problem with slowdown is not only on the getData side.

P.S. I’m using node v18.20.4. FYI

[aleksandra_budnik]

Hi @mr.fursy

Thank you for providing such a detailed explanation and sharing the code. It’s really helpful in understanding the issue. However, I still need a demo or a specific step-by-step guide to replicate the problem on my end. Could you provide that?

In general we run scrolling, edition, navigation performance tests (with stats and logs) for each version, so I believe that there are some specified settings that are used in your application that might have impact on the end result.

[mr.fursy]

Hi again, I can’t provide a step-by-step reproducible example because there is too much legacy code here. However, I inspected the code in the Performance tab (Chrome DevTools) and discovered that the functions collapseColumns, initGrid, and the overall logic in the useEffect that runs during table initialization (see the screenshot) are taking too long to execute.

I wrapped all the logic inside initGrid with console.time/console.timeEnd and noticed it takes approximately ~1300 ms. Then I started wrapping individual pieces of the logic inside initGrid with time/timeEnd and saw that the line

collapseColumns(hot, collapsibleColumns, headersMap)

takes about ~1500 ms. I wrapped the collapseColumns code block with time/timeEnd:

const collapseColumns = (
  hot: Handsontable,
  collapsibleColumns: GridSettings['collapsibleColumns'],
  headersMap: number[],
) => {
  if (collapsibleColumns && (collapsibleColumns as DetailedSettings[])?.length) {
    const collapsible: number[] = []
    (collapsibleColumns as DetailedSettings[]).forEach((col) => {
      hot.getPlugin('collapsibleColumns').collapseSection(col)
      collapsible.push(headersMap[col.col])
    })
    localStorage.setItem('collapsibleColumns', JSON.stringify(collapsible))
  }
}

---

const initGrid = (
  element: HTMLDivElement,
  options: GridSettings,
  setHot: Dispatch<Handsontable>,
  viewOnly: boolean,
  headersMap: number[],
  collapsibleColumns?: GridSettings['collapsibleColumns'],
) => {
  const hot = new Handsontable(element, options)
  const cells = options.cell || []
  collapseColumns(hot, collapsibleColumns, headersMap)

  hot.updateSettings({
    cells: (row: number, col: number) =>
      renderCells(row, col, cells, viewOnly),
    afterColumnCollapse: (...ars) => setColumnCollapse(...ars, headersMap),
    afterColumnExpand: (...ars) => setColumnCollapse(...ars, headersMap),
  })

  setHot(hot)
}

---

// Executes on the first render (initialization) of the table
useEffect(() => {
  if (refContainer?.current && (nestedHeaders.length > 0 || colHeaders)) {
    const optionsGrid: GridSettings = {
      data: spreadsheetData,
      columns: columnsFinal.map((el) => ({
        ...el,
        editor: el.editor === false ? NonEditableEditor : 'numeric',
      })),
      className: className,
      beforePaste: beforePaste || defaultBeforePaste,
      beforeAutofill: beforeAutofill || defaultBeforeAutofill,
      colHeaders,
      comments: true,
      rowHeaders: rowHeaders.length !== 0 ? rowHeaders : finalRowHeaders,
      afterGetColHeader: handleAfterGetColHeader,
      afterSelectionEnd,
      height,
      width,
      hiddenColumns: true,
      colWidths,
      ...(nestedHeaders.length > 0 && {
        nestedHeaders: TEST_MODE
          ? [
              ...nestedHeaders,
              data[0].map((_, index: number) => `${calcCellFromAlphabet(index + 1)}`),
            ]
          : nestedHeaders,
      }),
      rowHeights: 15,
      collapsibleColumns:
        (collapsibleColumns as DetailedSettings[]).length > 0 &&
        nestedHeaders.length > 0
          ? TEST_MODE
            ? (collapsibleColumns as DetailedSettings[]).map((el) => ({
                ...el,
                row: el.row - 1,
              }))
            : collapsibleColumns
          : false, // explicitly disable the plugin if there are no nested headers
      licenseKey: LICENSE_KEY,
      selectionMode: 'multiple',
      formulas: {
        engine: hyperformulaInstance,
      },
      beforeRenderer,
      cell: cellFinal,
      beforeChange: handleBeforeChange,
      afterChange: handleAfterChange,
      afterSelection,
      afterDeselect,
      beforeKeyDown,
      afterGetRowHeader,
      rowHeaderWidth,
      maxRows: spreadsheetData.length,
    }
    initGrid(
      refContainer?.current,
      { ...optionsGrid, language: ruRU.languageCode },
      handleSetHot,
      viewOnly,
      headersMap.current,
      collapsibleColumns,
    )
    if (toggleAllRef.current) {
      toggleAllRef.current?.addEventListener('click', toggleAllHeaders)
    }
    setIsInitSpreadsheet && setIsInitSpreadsheet(true)
  }
  return () => {
    refContainer.current = null
    handleSetHot(null)
    if (toggleAllRef.current) {
      toggleAllRef.current?.removeEventListener('click', toggleAllHeaders)
    }
  }
}, [])

---

// Executes when the table is updated
useLayoutEffect(() => {
  if (refContainer?.current && (nestedHeaders.length > 0 || colHeaders)) {
    const optionsGrid: GridSettings = {
      data: spreadsheetData,
      columns: columnsFinal.map((el) => ({
        ...el,
        editor: el.editor === false ? NonEditableEditor : 'numeric',
      })),
      height,
      width,
      cell: cellFinal,
      cells: (row, col) => renderCells(row, col, cellFinal, viewOnly),
      afterChange: handleAfterChange,
      beforePaste: beforePaste || defaultBeforePaste,
      beforeAutofill: beforeAutofill || defaultBeforeAutofill,
      afterGetColHeader: handleAfterGetColHeader,
      afterSelectionEnd,
      beforeKeyDown,
    }

    if (hotRef.current) {
      // Before updating nestedHeaders, save the indexes of the expanded cells
      // Rename the variable to avoid name collisions with the prop
      const collapsibleCols =
        JSON.parse(localStorage.getItem('collapsibleColumns') as string) ?? []
      const collapsible: DetailedSettings[] = []

      // Before updating, expand all columns so that the new values are set correctly
      if (isUpdateNestedHeaders) {
        optionsGrid.nestedHeaders = nestedHeaders
        hotRef.current.getPlugin('collapsibleColumns').expandAll()
      }

      // Update the table settings
      hotRef.current.updateSettings(optionsGrid)

      // After the update, restore the state of the collapsed columns
      if (isUpdateNestedHeaders && Array.isArray(collapsibleColumns)) {
        collapsibleCols.forEach((col: number) => {
          const columnIdx = headersMap.current.findIndex(
            (nestedHeaderIdx) => nestedHeaderIdx === col
          )

          if (columnIdx !== -1) {
            const collapsibleColumn = collapsibleColumns.find(
              (item) => item.col === columnIdx
            )

            if (collapsibleColumn) {
              collapsible.push({
                row: collapsibleColumn.row,
                col: columnIdx,
                collapsible: true,
              })
            }
          }
        })

        collapseColumns(hotRef.current, collapsible, headersMap.current)
      }
    }
  }
}, [
  refContainer,
  height,
  width,
  columnsFinal,
  nestedHeaders,
  data,
  collapsibleColumns,
  cell,
  afterChange,
  spreadsheetData,
  afterGetColHeader,
])

---

I found a method called batch that allows combining several operations into a single render, and I implemented it as follows:

const collapseColumns = (
  hot: Handsontable,
  collapsibleColumns: GridSettings['collapsibleColumns'],
  headersMap: number[],
) => {
  if (collapsibleColumns && (collapsibleColumns as DetailedSettings[])?.length) {
    const collapsible: number[] = []
    const plugin = hot.getPlugin('collapsibleColumns')
    const columnsToCollapse = collapsibleColumns as DetailedSettings[]

    // Disable re-rendering during the operations
    hot.batch(() => {
      for (const element of columnsToCollapse) {
        const col = element
        plugin.collapseSection(col)
        collapsible.push(headersMap[col.col])
      }
    })

    localStorage.setItem('collapsibleColumns', JSON.stringify(collapsible))
  }
}

It looks odd—like a hack.

Using the following timers:

const initGrid = (
  element: HTMLDivElement,
  options: GridSettings,
  setHot: Dispatch<Handsontable>,
  viewOnly: boolean,
  headersMap: number[],
  collapsibleColumns?: GridSettings['collapsibleColumns'],
) => {
  console.time('initGrid')
  const hot = new Handsontable(element, options)
  const cells = options.cell || []
  console.timeEnd('initGrid')

  console.time('collapseColumns')
  collapseColumns(hot, collapsibleColumns, headersMap)
  console.timeEnd('collapseColumns')

  console.time('updateSettings')
  hot.updateSettings({
    cells: (row: number, col: number) =>
      renderCells(row, col, cells, viewOnly),
    afterColumnCollapse: (...ars) => setColumnCollapse(...ars, headersMap),
    afterColumnExpand: (...ars) => setColumnCollapse(...ars, headersMap),
  })

  setHot(hot)
  console.timeEnd('updateSettings')
}

I obtained the following results:

initGrid: 190.781982421875 ms
collapseColumns: 211.69287109375 ms
updateSettings: 165.81884765625 ms
initGrid: 32.54296875 ms
collapseColumns: 0.002197265625 ms
updateSettings: 20.9921875 ms

It seems to me that it should be possible to speed things up and make the logic in initGrid more efficient. The following blocks:

const hot = new Handsontable(element, options)
const cells = options.cell || []

and

hot.updateSettings({
  cells: (row: number, col: number) => renderCells(row, col, cells, viewOnly),
  afterColumnCollapse: (...ars) => setColumnCollapse(...ars, headersMap),
  afterColumnExpand: (...ars) => setColumnCollapse(...ars, headersMap),
})

are taking too long to execute. After updating the collapseColumns function, the performance snapshot looks like this.

Can you suggest any improvements or ideas on how to optimize this further?

[mr.fursy]

Oh gosh. I also noticed that the response after editing a cell in my table dropped by 3 times (the logic that is executed for the afterChange hook). On 13.1.0 it’s about ~0.5s, on 15.1.0 ~1.5s.