Question for Appsheet Jedi's Only - Formula performance

I’m trying to speed up some Apps by refactoring various formulas. One I’m using everywhere is this one which gets the logged in users security level:

ANY(Select(Staff[Type],[Login Email]=UserEmail())="Customer"

Based on advice from @MultiTech_Visions I’ve created a slice called Current_User with a formula of

[Login Email]=UserEmail()

This wil create a single row slice. But now I’m stuck on the optimum way to reference it. Which of these is fastest?

IN("Customer",Current_User[Type])
or 
ANY(Select(Current_User[Type],TRUE))="Customer"

Obviously can’t use

Current_User[Type]="Customer"

Since Appsheet will assume Current_User[Type] is a list even though we know it isn’t

Which is best or can someone suggest something else?

2 Likes

EDITED: Don’t read what this crazy guy (me) just said below. If you do, forget it immediately!!

Strike from mind
My understanding is an IN() function will be transformed into an ANY(SELECT()).  By implementing the ANY(SELECT() straight away you eliminate that extra step.

FWIW, the ANY(SELECT()) could be written as follows:

ANY(Current_User[Type]) = "Customer"

But I suspect this might still be transformed into the ANY(SELECT())

3 Likes

One idea that might be even faster, is to use USERSETTINGS to set the users Type value. I believe retrieving the value from USERSETTINGS would be slightly faster but I have no empirical data to support that claim.

3 Likes

Thanks for the reply @WillowMobileSystems Yeah I think the issue here is going to be we have no idea of teh underlying code for the functions we use. Does @Steve know anyone to copy in who can awser best

3 Likes

I’ve not heard this before, I’m curious if there was a post or something I missed you might find a link to?

I ask because I use IN() all over the place for my Current_User stuff.

image

  • I have formulas like this all over my apps, and I don’t really notice any performance issues.
    • No hang-ups, or freezes, where the device is calculating a ton of things
    • No extraneous load times
2 Likes

I’ll search. I do remember it in a post quite a while ago.

Actually, as I am thinking about it now, it is most likely that the transformation of the functions into whatever is considered the lower level function, happens as an Interim step BEFORE final compilation.

Meaning, that the transformation steps most likely do not impact performance.

Our apps run on the final compilation.

This is true for most development languages. They go through a series of steps of compilations and linking before arriving at the machine level language. Even interpreted go through this, it’s just done on the fly!

It’s been a while since I have had to deal with any type of compilation. Thanks to AppSheet!!

1 Like

These are not at all equivalent.

2 Likes

In what way? Are you missing the part where Current_User is a slice of ONLY the logged in user?

2 Likes

Yes, I did miss that! Oops!

GIVEN THAT!..

Yes, they are equivalent. I’ve previously expressed my preference for the IN() approach.

4 Likes

@praveen maybe might weigh in???

1 Like

I’m curious if there is any technical reason for your preference? Maybe aesthetics or simplicity?

1 Like

Yep, that.

2 Likes

hahaha :laughing: Love it!

On another note… just musing here…

1 Like

You’re absolutely thinking about it in the correct way!

HOWEVER…(you knew it was coming)…there is much more to determining if a value is “in the list”. The system doesn’t know there is only a single value. It has to:

  1. Get the first list item value.
  2. Compare list item to value
  3. Check if more values in list
  4. Loop or end.

There is also extra overhead to setting up and tearing down the ability to loop - generally not noted in performance evaluations.

This is actually slower than “getting the First value from list” - no looping needed - and then comparing the value to the list item value. See the difference?

In the end, us app creators shouldn’t be worried about two effectively equivalent expressions regarding performance. We can say that these two expressions are equal in performance.

2 Likes

As I think about this more, I am mistaken and you are correct. And I can’t understand why I even thought such a thing??? The way both of these are transformed in the underlying language is likely completely different though I imagine in the complied version they coalesce into something very similar since they are so similar functionally. Regardless…I’ll update my post.

I’d be interested to see if this was correct.

But maybe you’re on to something else. Maybe if we did all the list stuff in the UserSettings e.g. ANY(Select(Current_User[Type],TRUE)) then in the tables we only have to do Usersettings(User_Type)="Customer". So if the formula is used multiple times its only doing a comparision of text against text.

Correct. I would think grabbing the value from USERSETTINGS is faster than running the expressions each time. Of course all this depends on implementation and optimization. But sometimes logical thinking does win!!

1 Like