Virtual Column Performance

Austin
Participant V

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.

0 9 554
9 REPLIES 9

What app formulas are you using with these columns?

Austin
Participant V

Lookup(RIGHT([HiringReq],7),โ€œRequisitionโ€, โ€œRequisitionIDโ€,โ€œJob_Titleโ€)
And Iโ€™m grabbing different columns with the same formula for all 3.

@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
Participant V

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โ€?

@Austin_Lambeth
Correctโ€ฆLOOKUP returns the very first value that matches the search criteria

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

Austin
Participant V

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?

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
Participant V

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.

Top Labels in this Space