App performance¶
By keeping the following in mind, you can create apps that work more efficiently.
Calculation formulas placed on sheets should be minimal¶
Formulas on the sheet are calculated the first time they are displayed and when the value in the related cell changes.
For example, when the formula set in cell A1 as shown in the figure below refers to the cell in which the formula is set outside the screen display range (column F and beyond),
When cell A1 is displayed, the formulas of the cells referenced in cell A1 are also calculated (※) in a chain, and processing takes time.
※ F1:F50 is calculated, and all the formulas G1:J1 to G50:J50 are calculated.
Similarly, if the formula refers to a cell in the range that lists the data,
When a large number of cell values are changed by searching or clearing the list, all formulas that refer to them are recalculated.
If these factors unintentionally recalculate a large number of formulas, it will take some time to process.
Please be careful to use the minimum number of formulas.
Pay attention to other sheet references and action set calls¶
If a sheet is referenced by other sheets, each time the action set for any of the referenced sheets is executed,
Formulas in the referenced sheet may also be recalculated and may take some time.
For example, if sheets X and Y refer to sheet A (“=sheet A!A1”), if the cell value of
sheet A is changed or the action set of sheet A is executed, sheet X , Y formula is also recalculated.
If you have a lot of formulas in sheets X and Y, these recalculations will take a long time.
This problem can be avoided by implementing as many sheets as possible.
For registering, updating, and deleting a large number of records, use the 'Batch execution of data updates'¶
Actions that operate on the database access and disconnect the database each time it is executed.
When registering, updating, and deleting data consecutively, placing them in the 'Batch execution of data updates' action reduces the number of database accesses and improves processing speed.
See also
Regarding 'Batch execution of data updates' action, check Control transactions
Set the Index¶
Searching a table with a large amount of data may take some time.
In such a case, by setting indexes on columns that are often used as search conditions and sort conditions, the processing speed improves.
See also
Regarding Index, check Tuning For Searching Table (Set Index)
Use VLOOKUP when searching for specific values¶
CELF’s VLOOKUP is specially tuned and runs fast.
For example, to check for the existence of a specified value in a list, it is faster to use VLOOKUP than to use COUNTIF.
In Excel, approximate match search (4th argument: true) is faster, but in CELF, exact match search (4th argument: false) is faster.
Attention
※ Wildcards cannot be used for the search value (first argument).※ The match between the search value (first argument) and the search range (second argument) does not depend on the cell format setting. Note that values consisting of numbers only are evaluated as numbers.Example)=VLOOKUP("123",…) will also hit "00123".
Specify the cell range when performing the same process on consecutive cell ranges¶
When performing the same operation on a contiguous cell range, it is faster to specify the cell range and process it collectively than to process it one by one using a repeat action.
- Example) To set the same format for cell range C3:C100
- Example) When the same value is set in cell range W5:W100 (same for clear)
Attention
Note that you cannot set a range value to a range.Example)In 'Set value [=A5:A100] into cell [W5:W100]', #VALUE !, which is the result of the formula =A5:A100, is set in all cells of W5:W100.
Take time to aggregate or sort joined multiple tables¶
Aggregating and sorting data in a database can take a large amount of processing time, depending on the content and volume of the data.
Be especially careful when joining multiple tables for aggregation and sorting.
This is because the database needs to process the maximum 'number of data multiplied by the number of data in each table to be joined' for aggregation and sorting.
For example, if you join three tables, Table A with 100 data, Table B with 200 data, and Table C with 300 data, the maximum number of data to be processed would be 6,000,000 data (100 x 200 x 300).
Even if the number of data in each table is small, the number of data required for processing increases rapidly as the number of tables to be joined increases, and the response may deteriorate significantly.
Format adjacent cells as uniformly as possible¶
If there are many formatted cells, or if the layout does not have consistent formatting for adjacent cells, the sheet will be larger and take longer to display.
In such cases, consider reducing the formatting or creating a layout in which adjacent cells have the same formatting.
In the above layout, the left and right cells have different display formats, and the top and bottom cells have different background colors, so adjacent cells are not formatted uniformly.
In such cases, unifying the formatting of adjacent cells, such as by using the same background color for the list section, will reduce display time.
Reducing the number of formatted cells can also reduce display time.
To cancel the format already set, set the Number to "General", the Font to "Regular", the Borders to "None", and the Interior to "No color".
You can also make the sheet larger to display a lot of data and use paging techniques if you have many formatted cells.
Hint
For sheets created with older versions of CELF (3.2.2 or earlier), display time may be improved by re-saving the sheet.See Change of sheet saving format in version 3.3.0 for details.