This all started because I noticed I had a table that was taking 27+ secs to sync. First I assumed it was because the subtable Jobs had 15000+ rows. So I did some digging and now I canโt work out why one formula seems to be REALLY inefficent.
I have two virtual column formulas that both reference a list column called [Related Jobs]. This relates to a subtable called jobs. The App takes just 0.3 secs to calculate [Related Jobs] and work out which Jobs records related to each record in the parent table (Site). I also have this formula called [Last Collection Date] that takes 0.4secs to find the latest timestamp [WeightTS] a Date/Time column.
DATE(
MAX(Select([Related Jobs][WeightTS],AND(
ISNOTBLANK([Weight]),
[Weight]>0
)))
)
Finally I have this formula called [Last Collection Weight]. Which it basically the same as the one above. Yet this takes 20.8secs to complete. Since its essentially the above formula just wrapped in an ANY(Select(โฆ function to get the [Weight] column instead.
ANY(Select([Related Jobs][Weight],[WeightTS]=
MAX(Select([Related Jobs][WeightTS],AND(
ISNOTBLANK([Weight]),
[Weight]>0
)))
))
I know that I could make the second column reference the first instead of running essentially the same formula. I also know from @Steve that MAXROW() might be a better option. But Iโd be interested in why this second formula is Sooooo inefficent. When just from looking at it youโd assume it would take twice as long as the first formula, not 40+ times longer.
Iโve checked out performance manager for a few different users (PC & Phones) and this forumla is always a huge amount slower than the first one. So i donโt think its related to anything like delta-sync
LinkedIn
Twitter
