Is it best to use LOOKUP or a Reference column for the quickest sync time?
I thought I read something on here a while ago, that I can’t find now, which said LOOKUP was quicker.
But now I have some LOOKUPs and appsheet is warning me, which is very helpful,
Note, this expression could significantly impact sync time.
No message comes up with a Reference expression, so I’m assuming that doesn’t impact sync time.
Just checking before I change everything.
Thanks
In general. a Ref is likely to be slightly faster, but you’d likely only ever notice a difference in expressions that involve large data sets. Sync time is directly tied to the expressions that involve large data sets.
We might be able to offer better advice if you shared the expression?
I was using this
IF(LOOKUP([_THISROW].[Order Number],“Sundries Sales Orders”,“Order Number”,“Qty Picked 1”)=0,“”,LOOKUP([_THISROW].[Order Number],“Sundries Sales Orders”,“Order Number”,“Image 1”))
But I can change to this as I can make the Order Number a reference to the other table
IF([Order Number].[Qty Picked 1]=0,“”,[Order Number].[Image 1])
There are 50 of these on each row.
And 3 more similar expressions, which could be LOOKUP or a reference each with 50 on the row.
So 200 LOOKUPS or references for each new record.
They need to sync the app quite frequently to get new entries from the Sundries Sales Orders table, so the faster the sync the better.
If the Ref already exists, use it.
In your particular case, a Ref would likely be more efficient on the assumption (because I don’t know but do suspect) that AppSheet remembers the target of a Ref and so doesn’t have to look it up on each re-use, whereas a LOOKUP() gets re-run each time.
For instance in this expression:
IF([Order Number].[Qty Picked 1]=0,"",[Order Number].[Image 1])
[Order Number].[Qty Picked 1] is equivalent to LOOKUP([_THISROW].[Order Number], some-table, its-key-column, "Qty Picked 1"). I suspect when a Ref is used, AppSheet remembers the row the Ref references. So, when [Order Number].[Image 1] is then evaluated, AppSheet doesn’t have to do another LOOKUP(), it just reuses the remembered row. This re-use eliminates the entire table scan done by the second LOOKUP(), cutting the total time in half! If Order Number is dereferenced across many columns, each re-use eliminates a LOOKUP(), speeding up the app.
Remembering values for re-use like this is called “caching”.
Going back to my original statement:
Steve:
In general. a Ref is likely to be slightly faster, […]
This is likely true comparing the performance of a single Ref to a single LOOKUP(), but not when a Ref is reused.
Thanks Steve.
That makes sense, so I think i’ll try changing to Refs.