Make IF(AND(...)) and IF(OR(...)) finished faster

[Swoopy]

In some case, when you have expressions like

IF(
   OR(condition1, condition2, condition3),
   do1,
   do2
)

change them to

IFS(
   condition~highest possible,
      do1,
   condition,
      do1,
   condition~lowest possible,
      do1,
   TRUE,
      do2
)

And in some case, when you have expressions like

IF(
   AND(condition1, condition2, condition3),
   do1,
   do2
)

change them to

IFS(
   NOT(condition~lowest possible),
      do2,
   NOT(condition),
      do2,
   NOT(condition~highest possible),
      do2,
   TRUE,
      do1
)

[Marc_Dillon]

Both AND and OR will "short circuit" when they can, so doing this is not really beneficial performance-wise.

Smartly ordering the conditions within the AND/OR is definitely beneficial, by putting the least expensive conditions first.

[Swoopy]

These were tested thru 50,000 rows+, that it noticeably works. The AND and OR processing is suspicious and seems no 'short circuit', like they should (based on my testing). Beside no users know exactly how AppSheet's AND/OR/etc are coded.

Ordering the row possibility would be more benefit over the ordering of formula complication whenever tables have some group of rows having significantly more possibility among others.

[Marc_Dillon]

Care to share those test results and methodology?

[Swoopy]

It's better to try by yourself (with real-world huge datasets), if you really care.

[Marc_Dillon]

Here is Steve relaying a statement from an Appsheet developer that these expression do short-circuit.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Expression-efficiency-sequence-of-arguments/m-p...

------

I indeed have tested it myself. I inputted the following expression into a 300-record table, that does a nested select on an 11k table. So a total of over 30 billion iterations (300 * 11000^2).

The app failed to sync due to timeout. Simply changing the "TRUE" to "FALSE" made the app sync normally.

[Swoopy]

Thanks for testing.

1. What if FALSE is under the long expression. I'm not sure the long expression is skipped in processing.

2. The simply logical constant TRUE does not imply, when we have like 3 complicated expressions, if or how the short circuit works or chooses which expressions to be skipped/ignored.

3. In case of AND() it would skip the rest if the first expression returns FALSE, so if the expression with highest possibility of FALSE result is at topmost, this makes the AND(........) finish faster though.

[Marc_Dillon]

1. No, it wouldn't, because that's not how short-circuiting works. The system has no idea which of your conditions is the least expensive or most prone to failing w/ an AND, or passing w/ an OR. It's your job to put them in the right order. Same idea as your original post.
2. See above, that's not how short circuiting works.
3. Correct.