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?
EDITED: Donโt read what this crazy guy (me) just said below. If you do, forget it immediately!!
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())
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
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โ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!!
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.
can someone suggest something else?
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.
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.
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.
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!!
Which of these is fastest?
IN("Customer",Current_User[Type]) or ANY(Select(Current_User[Type],TRUE))="Customer"
These are not at all equivalent.
These are not at all equivalent.
In what way? Are you missing the part where Current_User is a slice of ONLY the logged in user?
Yes, I did miss that! Oops!
GIVEN THAT!..
Yes, they are equivalent. Iโve previously expressed my preference for the IN() approach.
Iโve previously expressed my preference for the IN() approach.
Iโm curious if there is any technical reason for your preference? Maybe aesthetics or simplicity?
Maybe aesthetics or simplicity?
Yep, that.
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"
@praveen maybe might weigh in???
On another noteโฆ just musing hereโฆ
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:
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.
User | Count |
---|---|
40 | |
36 | |
34 | |
23 | |
17 |