I have a column formula that works really well with one single caveat… It takes 10 seconds per new entry to calculate. When I add 10 new entries, the computer essentially freezes for everyone in the doc for ±2 minutes, which makes collaboration almost impossible.
The column that I’m working with has 500 entries and the formula tries to look for clashing/overlapping times for a specific resource. There’s a separate resource column with 100 entries.
The formula is:
thisTable.Filter(
([Day of the week]=thisRow.[Day of the week]) AND ([Resource]=thisRow.[Resource]) AND [This row resource not blank] AND (Status=Active) AND (thisRow.Status=Active) AND (thisRow.Project!=Project)
AND ((thisRow.[Start] > [Start] AND thisRow.[Start] < End)
OR ([Start]>thisRow.[Start] AND [Start]<thisRow.End)
OR (thisRow.End > [Start] AND thisRow.End <End)
OR (End>thisRow.[Start] AND End<thisRow.End)
OR(thisRow.[Start]=[Start] AND thisRow.End=End))
)
Start = the start time
End = the end time
This row resource not blank = true/false (separate column that checks whether resource row is not blank)
Project - A “head” project, which this activity is part of.
Would love to learn how to make it more efficient!
Hi @Rokas_Jurkenas
Would you please share your doc or a sample of it?
I can test “faster” formulas but i need a doc to test those
Just with eye i would say that all those “or” could be better optimized, but i would prefer to test it on a live doc
P.s. from my personal experience the “slowness” of filters is because of two reasons, the first is how “long” is the formula, and second, how many rows do you have to check
I would personally create an intermediary table that filter the main part of your datas, and then re-filter those with another formula, this could improve your experience.
i’m currently using a formula that need to check 5000+ rows continuously and it’s one of the worst part of my doc, sometimes it take minutes
Short answer: you cannot make this any more efficient. Trust me, I was doing the exact same thing a few weeks ago for a client.
You can optimize the time overlap condition to simply: Not(Start >= thisRow.End OR End <= thisRow.Start)
but it will not improve the formula significantly because the filter recalculation complexity is still O(N^2 * K) for the whole column, i.e. it will recalculate for every existing row every time you add a new row, and will become more and more slow with every added row.
One way to address this is to actually delete the column formula but only calculate it on demand for new rows (since you don’t care for this being updated for previous rows, right?) You can do this by e.g. making it an input formula of the same type (lookup to the same table, multiple items enabled) and adding this formula as a formula for new rows instead of a column-level formula. Or better, instead of a “new row added” formula make a dedicated row-level button to calculate this just for this row on demand (so that you can press that button once again when you resolve the conflict, to re-check). This way you will not do unnecessary recalculations, dropping complexity to just O(N * K) where N is the number of existing rows and K is the number of the rows you’re adding in the current batch.
