Performance Question for VC: One Column Checking Condition vs Multiple Columns Checking Same Condition

The question is in regards to performance on virtual columns when multiple VCs check the same condition on whether or not to execute a calculation.

The use case is that I currently have at least two VCs that have an IFS() formula checking the same condition to decide if the column value is either null or executes a calculation as follows:

IFS(AND([ReportingFrequency] = "Monthly", TODAY() <= DATE(CONCATENATE(MONTH([ExpirationDate]), "/", DAY([ExpirationDate]), "/", YEAR([ExpirationDate]) +5))),
SELECT(Mthly_Report_Dates[Formatted Date], 
AND(
[Date] > EOMONTH([DateOfOrder], -1),
[Date] <= [ExpirationDate],
[Date] <= [CancellationDate],
[Date] <= IF(DAY(TODAY()) > 10, EOMONTH(TODAY(), -1), EOMONTH(TODAY(), -2))
)
) - SORT(UNIQUE([Related WaterUseDatas][Period]), FALSE)
)

The portion I am particularly focusing on is this expression:
TODAY() <= DATE(CONCATENATE(MONTH([ExpirationDate]), "/", DAY([ExpirationDate]), "/", YEAR([ExpirationDate]) +5))

I am wondering if there is a significant performance savings if I would add a VC (Yes/No type) to evaluate this condition first and then reference this VC in the other Virtual Columns. Since I only have two VCs evaluating this condition currently I thought maybe the performance savings would not be as great, but I very likely have the need to evaluate this same expression in another 3 VCs bringing the total to 5. Also, rather than using CONCATENATE() for determining a date that is 5 years after the [ExpirationDate] would it be more efficient to use EOMONTH([ExpirationDate], 60)?

Thank you for any input.

0 1 97
1 REPLY 1

Yes, I think youโ€™ll get a small performance boost. โ€œSignificantโ€? Probably not, but thatโ€™s mostly because the condition itself is not very expensive (no SELECTs). An important point here though is that adding the VC just for the condition isnโ€™t going to cost any more. So you might as well do it and simplify the other expressions, especially if you think youโ€™ll use it further down the line.

Hmmm. Thatโ€™s an interesting question. Iโ€™d assume the EOMONTH() expression is actually doing some sort of lookup on an internal appsheet database that holds all dates and months. Your CONCATENATE might actually be more performant in that case, since youโ€™re just adding a static +5 to the year component. However you may have to be careful that youโ€™re not creating an invalid date, i.e. Feb 30th. In either case, I doubt the difference would be significant, so donโ€™t worry about it too much. EOMONTH is certainly a simpler/shorter/easier-to-read expression, and thatโ€™s worth a lot on its own.

Top Labels in this Space