How to improve the performance of the application #1241
Replies: 3 comments 7 replies
-
If the following application structure is a must and tips from https://handsontable.com/docs/javascript-data-grid/performance/ (Performance tutorial) do not help much, it might be better to move the heaviest calculations to the server (Hyperformula also works on the backend). |
Beta Was this translation helpful? Give feedback.
-
Hi @nuwanb-swivel @nilesh-swivel, The profiling of the provided example indicates that ~70% of the build-time is spent on formula evaluation. The data contains 5000+ calls to the aggregation functions ( Recommended optimization
With this amount of data, one linear search is ok, but 4000 would be slow. The best solution is to reorganize your data to make the binary search possible. It will make a huge difference. If the
Other ideas for optimizationReduce the number of heavy function calls
It can be simplified to:
Optimize the creation of named expressionsYou can add named expressions on build-time. This will be faster than adding them when the engine is already built. const namedExpressions = [
{ name: "A", expression: "=1+1" },
{ name: "B", expression: "=1+1+1" },
];
const hf = HyperFormula.buildFromSheets(sheets, options, namedExpressions); Calculate the sums and counts more efficientlyYou can create a custom function that will calculate the exact value that you need in a more efficient way. For example, calculate both count and sum on a single run or do something even more specific to your requirements. Run HyperFormula server-sideServers usually have more computation power than PCs, so the calculations will run faster.
It's possible to pass only the changed values after each operation, as HyperFormula reports which cells have changed. You don't need to send the full data every time. Suspending calculations for selected sheet
It is an interesting idea. HyperFormula does not have such a feature at the current stage of development, but it is an open-source project, so you can work on it yourself. I'll be happy to assist you ;) I apologize for such a late reply. I hope this answer will be beneficial for making HyperFormula work for you. If you need help implementing these ideas or work on other solutions in greater detail, please contact our sales team for information on our consulting services. |
Beta Was this translation helpful? Give feedback.
-
Hi @sequba Thanks for the detail explanation. I will try as you explained. I will post an update soon with the findings |
Beta Was this translation helpful? Give feedback.
-
We have an application with around 5000 records on the ‘aggregation’ sheet. We also have 8 more sheets that contain some codes, each defined as named expressions. These codes are used on the aggregation sheet. These codes will be used inside the aggregation sheet on different columns as a text or inside the formula. We need to calculate the usage count for each code on each sheet and also calculate the total if the code is used in the aggregation sheet.
Formulas
Total
=IF(groups!C1="","0",IF(I1="N",SUMIF(aggregations!B:B,"*" & groups!C1& "*",aggregations!K:K),SUMIF(aggregations!B:B,"*" & groups!C1& "*",aggregations!M:M)))
Usage count
=IF(groups!C1="","0",COUNTIFS(aggregations!B:B,"*" & groups!C1& "*"))
It takes more than 5 seconds to build the Hyperformula instance using the ‘buildFromSheets’ method. The expected aggregation sheet could have more than 20,000 records, so the application build time is long.
Adding a record, deleting multiple recods and update the cell value to the aggregation sheet freezes the application and takes around long time to complete the operation.
I have created a demo on CodeSandbox and looking for a help :)
https://codesandbox.io/s/pivot-budget-aggregation-rx1kzt?file=/src/components/Container.tsx
Beta Was this translation helpful? Give feedback.
All reactions