Solved: Why is this formula 40 times slower than another o...

1minManager · 11-17-2021 08:14 AM

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

Steve

The MAX() expression visits every row in [Related Jobs] (N). The wrapping SELECT() performs that entire MAX() expression for each of every row in [Related Jobs] (N^2). That’s a big increase. Can you make the MAX() expression its own VC?

View solution in original post

Marc_Dillon

My only guess is that the DateTime comparison is expensive.

1minManager

I supose I could test TEXT([WeightTS]) but it would be equally odd if this actually made a difference

Steve

The MAX() expression visits every row in [Related Jobs] (N). The wrapping SELECT() performs that entire MAX() expression for each of every row in [Related Jobs] (N^2). That’s a big increase. Can you make the MAX() expression its own VC?

Marc_Dillon

Ignore my earlier comment, I guess I was caught in the same misunderstanding thought loop that you were (or just morning brain ).

SELECT() + SELECT() would be about twice as long.
SELECT( SELECT() ) is exponentially longer.

If you have 15k records, then you are executing a SELECT() query 15k^2 times, or 225 million times. And that doesn’t even take into account the number of records in the Table that you’re SELECT()'ing on.

Basically what Steve said, just said in another way.

1minManager

Awesome repsonses @Marc_Dillon & @Steve. I’ve done as recomended and added a third virtual column to reduce the duplication. Here are the formulas & sync times (in case this helps anyone else)

New - [Last Collection] = 0.33sec

MAX(Select([Related Jobs][WeightTS],AND(
	ISNOTBLANK([Weight]),
	[Weight]>0
)))

Updated - [Last Collection Date] = 0.01sec

DATE([Last Collection])

Updated - [Last Collection Weight] = 0.3sec

ANY(Select([Related Jobs][Weight],[WeightTS]=[Last Collection]))

Why is this formula 40 times slower than another one