Virtual Column Performance

[Austin]

I have an app that has 3 virtual columns that are taking around a second each. There are 30k rows in this table so aside from cutting down the rows, which I am also working on, does anyone have any tips for making these columns less expensive? The columns are a lookup of another table in my app, I have a concatenated column of the 3 that I need already so would pulling that column and splitting it be faster than 3 lookups? These values are based on a foreign key that can be changed so adding them to the original table is not an option.

[Aleksi]

What app formulas are you using with these columns?

[Austin]

Lookup(RIGHT([HiringReq],7),“Requisition”, “RequisitionID”,“Job_Title”)
And I’m grabbing different columns with the same formula for all 3.

[LeventK]

@Austin_Lambeth
Provided your formula evaluates a value of “AAA” for example and you have more than 1 row which this expression can evaluate to the same value, then it always returns the result from the very 1st record from your table.

[Austin]

So you’re saying it remembers if it’s evaluated this formula with “AAA” and doesn’t re-execute the formula for any later rows that have a value of “AAA”?

[LeventK]

@Austin_Lambeth
Correct…LOOKUP returns the very first value that matches the search criteria

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Steve]

Austin_Lambeth:

it remembers if it’s evaluated this formula with “AAA” and doesn’t re-execute the formula for any later rows that have a value of “AAA”?

What you describe here is result caching, which AppSheet does not do.

[Austin]

That was what I thought. So would it be of use to have one lookup virtual column that contains all 3 values that I need, which I already have in the requisition table for other needs, then use a split to get the values I need in my table?

[Steve]

If you already have the values either (1) in the same row, or (2) in a different, small table, using those existing values would be vastly preferable to doing multiple lookups on a table of 30k+ rows.

Curious why you “have a concatenated column of the 3” values. Why not have them in an EnumList? Then you wouldn’t need to SPLIT() it to get the individual values.

[Austin]

The 3 values are in a header column to make them fit our UI better and to allow our users to use the header in a drop down selection.