Expression efficiency - sequence of arguments

[dbaum]

I'm working to optimize various expressions in places like virtual columns and security filters. I wonder whether anyone has guidance or experience regarding the following points related to the sequence of arguments within functions where some arguments don't need to be evaluated. Any known best practices seem worth applying when a function's arguments vary in complexity--e.g., simple equality vs. SELECT with complex criteria.

Examples

Sequence A	Sequence B
IF( {condition},  INDEX(SELECT({complexity}), 1), CONTEXT("Host") = "Server" )	IF( NOT({condition}),  CONTEXT("Host") = "Server", INDEX(SELECT({complexity}), 1) )
OR( INDEX(SELECT({complexity}), 1), CONTEXT("Host") = "Server" )	OR( CONTEXT("Host") = "Server", INDEX(SELECT({complexity}), 1) )

IF, IFS, SWITCH

The help articles for these functions all state that the only result argument that is evaluated is the one associated with the outcome of the condition argument. For example:

• From the IF article regarding the function's second argument: "An expression to be evaluated only if the is-true? expression evaluates as TRUE."
• From the IFS article regarding the function's second argument: "An expression to be evaluated only if the immediately preceding is-true? expression evaluates as TRUE."
• From the SWITCH article regarding the function's third argument: "An expression that is evaluated and returned if the associated expected-value is a match."

While the phrasing is more explicit in some articles than others, they all seem to literally state that result arguments that don't need to be evaluated are indeed not evaluated. If anyone knows differently, please advise.

OR

I don't find any reference to whether all arguments are always evaluated for the OR function. Perhaps evaluation ceases as soon as one argument evaluates to true? Does anyone know?

[gregdiana1]

Well in terms of using the OR it has to physically do both evaluations to determine the OR and thus may be time consuming.

In terms of the IF it is best to choose the {condition} which requires least calculation. So whether the condition is true or false the least time consuming result CONTEXT should be effected. ie only do work when you have to so for the IF the Sequence and condition go hand in hand.

Anyway that my bit!

[dbaum]

---

@gregdiana1 wrote:

Well in terms of using the OR it has to physically do both evaluations

---

Do you know that's the case? For example, in an expression like OR(condition1, condition2) if condition1 is true, does condition2 even get evaluated?

---

@gregdiana1 wrote:

In terms of the IF it is best to choose the {condition} which requires least calculation

---

Yes, that's surely a critical factor. Taking at face value the help articles' description that irrelevant result arguments are not even evaluated, efficiency is wholly driven by the condition argument. 

[Marc_Dillon]

Should be easy to test this yourself. Open up your biggest app. Record the original sync time. Create a new virtual column with an egregiously over-complex nested SELECT that will slow your app way down. Record the new sync time. Now change that VC expression to be OR( TRUE , {old-expression} ). See if the sync time goes back to normal or not.

---

@gregdiana1 wrote:

in terms of using the OR it has to physically do both evaluations to determine the OR

---

No, that's incorrect. You're thinking of AND perhaps?

[gregdiana1]

@Marc_Dillon 

see: https://calcworkshop.com/logic/rules-inference/

In terms of any OR statement it makes once any argument is found to be true testing the remainder is irrelevant. as noted here https://learn.microsoft.com/en-us/cpp/cpp/logical-or-operator-pipe-pipe?view=msvc-170

see

https://en.wikipedia.org/wiki/Short-circuit_evaluation

[dbaum]

---

@Marc_Dillon wrote:

Should be easy to test this yourself

---

Yes, of course. Posted here first in case others were able to point me to existing documentation that I hadn't found in my searches, as has sometimes happened. Thanks for detailing a targeted testing procedure.

[SkrOYC]

Hey @dbaum, I appreciate the time you spend on making sure this details about the platform are documented or tested for us to use the best approach. Your way of presenting the need shows that you really are interested to help and not just get a quick answer for yourself.

I suggest you to have an app just for this testing purposes instead of a production one.
There is a good tool to generate fake data so you can test this real life scenarios with big datasets without scrubbing your head for possible record names or emails, etc. Just in case it helps:

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

I use it for my last sample app regarding report generation via HTML/CSS.

---

My take on this topic is what common sense drives to so I won't add much comments and suggest to do some testing to find out the right answer (although there are always some variation that are out of our control)

[dbaum]

---

@SkrOYC wrote:

I suggest you to have an app just for this testing purposes instead of a production one.

---

Yes, that (standard practice?) is definitely critical. In case others find this comment and hadn't realized the value of this approach, I'll note as a point of reference that I find it useful to have both of the following:

• Sandbox apps purely for trying out things in a vacuum
• Non-production instances of production apps, which I sync using a procedure inspired by @Steve that leverages the app upgrade feature

---

@SkrOYC wrote:

There is a good tool to generate fake data

---

No kidding! I love Mockaroo, which I learned about somewhere in this community and use frequently.

[WillowMobileSys]

---

@dbaum wrote:

I don't find any reference to whether all arguments are always evaluated for the OR function. Perhaps evaluation ceases as soon as one argument evaluates to true? Does anyone know?

---

This is known as "logical short-circuiting".  In the case of an OR, only one of the conditions must be TRUE for the entire OR to be TRUE.  Short-circuiting adopts the idea that as soon as a TRUE condition is found, none of the rest of the conditions needs to be inspected - the OR is already TRUE.

I have specifically asked years ago if logical short-circuiting was used in AppSheet.  The answer, from AppSheet developers was "NO".  But they may have been answering specifically in terms of the platform implementation and may not have considered the underlying language used.

I do know that AppSheet uses C# language (probably among others) to run, at least part of, the platform. I also  know that C# performs logical short-circuiting.  So...IF our expressions are evaluated in C# at the system level, by transient properties,  our expressions would also benefit from logical short-circuiting.

I DON'T know if C# is used for evaluating our expressions.  AND I have not been able to think of a way to test if short-circuiting is actually applied.

So for now I think we have to assume the answer is NO.

SIDE NOTE:  For any logical expression's such as IFS or nested IF's, if possible, it is always advised to place the most frequently matching conditions first.  This avoids testing unnecessary conditions just to get to the "usual" matching one.  Of course, this is not always possible as placement of the tests sometimes matters.

[dbaum]

---

@WillowMobileSys wrote:

This is known as "logical short-circuiting". 

---

Thanks for the terminology (and, @gregdiana1, for the related links).

Thanks also for the additional background from when you posed the question

---

@WillowMobileSys wrote:

For any logical expression's such as IFS or nested IF's, if possible, it is always advised to place the most frequently matching conditions first.

---

Another factor worth considering, although when the most common matching condition is inefficient there might be an argument for prioritizing any comparatively efficient conditions even if they're less common matches since that would speed things up when they are matched without slowing things down much when they're not.

.

[SkrOYC]

---

@gregdiana1 wrote:

In terms of any OR statement it makes once any argument is found to be true testing the remainder is irrelevant

---

Yes, but in your first comment it sounded like you though the opposite, that OR() would evaluate anything anyways. That's why Marc said that it is not the case

[gregdiana1]

True, as I did not realize he was referring to logical short-circuiting at the code level. Whether the AS OR() function does the same I would not know, but I would presume so.

[WillowMobileSys]

---

@dbaum wrote:

when the most common matching condition is inefficient there might be an argument for prioritizing any comparatively efficient conditions

---

Yes, I agree with that.

[SkrOYC]

---

@dbaum wrote:

• Sandbox apps purely for trying out things in a vacuum
• Non-production instances of production apps, which I sync using a procedure inspired by @Steve that leverages the app upgrade feature

---

💯

[Steve · Accepted solution]

I've asked about this specifically. I have been told by the developers that AND() and OR() do short-circuit at the first opportunity. I.e., AND() fails at the first subexpression that evaluates to FALSE; and OR() succeeds at the first subexpression that evaluates to TRUE. Note that this was not always the case, but was changed only within the last few years.

[dbaum]

That's really helpful to know. Thanks.

@lizlynch, it would be great to see these points documented explicitly in the relevant AppSheet help articles.