Will these changes to my Virtual Columns speed up my app?

[ghost5]

I'll start this with I am quite certain the answer is yes, but I want to check this before I plan and go ahead with any changes in the near future as the app is critical to our business so I need to find a solution that works the first time...

This app started out 2+ years ago as a simple spreadsheet for listing jobs/projects but grew into a project management app that now heavily relies on virtual columns that are calculated every time it opens. The app still functions well but is getting slower and slower to open and I need to do some maintenance to reduce the data that is spiralling out of control.

There are 1100 projects. For each project, a cost is split into 20 categories. For each category, the app pulls the relevant data from 50,000 timesheet entries, 12,000 expense entries and a couple of other smaller tables. The app then calculates a profit and loss on each project using all this collected data, using virtual columns (for near real-time costs), which slows it down and is not sustainable for long-term use.

For projects that have been closed for a year, I can mark them as no longer active but I would like to keep the historic total costs. I could manually go through these closed projects and add the totals of all timesheets and expenses etc as a "normal" column within the app, then delete the records. This will reduce the size of the large tables. (I have tried but failed to automate this in the past).

For the virtual columns that currently look like this,

IF(
[WON] = FALSE,
0-([VC - Sum of Managment] + [vc - sum of tendering] + [vc - sum of operations] + [VC - Sum of Commercial] + [VC - Sum of Meeting] - [VC - PC - Management Variation]),
[Management Fee] - ([VC - Sum of Managment] + [vc - sum of tendering] + [vc - sum of operations] + [VC - Sum of Commercial] + [VC - Sum of Meeting] - [VC - PC - Management Variation]))

If I put another IF statement in front of the above along the lines of

If the project is closed use the value in column xx, otherwise continue

Would this speed up the app? Does AppSheet stop all calculations once an IF statement is met or does it still continue to think about the rest of the formula?

Are there any other guides/videos that give other ideas on how to maintain large data sets?

Instead of deleting the timecard entries, would a security filter work to not load the data if a column is marked as "No", or am I best just deleting them now to stop problems in the future with the table getting too big?

Thanks,

[Steve]

---

@ghost5 wrote:

the app is critical to our business so I need to find a solution that works the first time...

---

Consider creating a copy of the app and testing there. Once satisfied, you can upgrade the original from the copy. This is how I do most of my development.

[Steve · Accepted solution]

---

@ghost5 wrote:

Does AppSheet stop all calculations once an IF statement is met or does it still continue to think about the rest of the formula?

---

If the IF condition evaluates to FALSE, the THEN action is not performed, so your approach would avoid the unneeded computation.

[ghost5]

I went ahead this, this weekend and archived 2000 projects. The app does load slightly faster now but unfortunately, our timecard records only started 12 months so archiving the old projects has had minimal impact so far... but this is a problem for me to keep my eye on.
The answer to my question is, Yes AppSheet does stop calculating if and IF statement is reached and the table does load (slightly) faster now.

[Steve]

---

@ghost5 wrote:

Instead of deleting the timecard entries, would a security filter work to not load the data if a column is marked as "No", or am I best just deleting them now to stop problems in the future with the table getting too big?

---

Why wouldn't you use a security filter? I'd use a security filter.

[ghost5]

I put security filters on the table but I never spotted that it turned off fast sync (or whatever it is called). This created problems as other users where taking the key value and overwriting what other people had input, so I turned them back off.

[Steve]

---

@ghost5 wrote:

Are there any other guides/videos that give other ideas on how to maintain large data sets?

---

Scale using data partitions