How to restrict an "expensive" expression

AppSheet AppSheet Q&A
1

Hello I have an expensive MAXROW expression that queries a database to identify the most recent record (see below). I’m looking to restrict the expression as much as possible while ensuring it functions as needed and thought to only run the expression when the 2 views are active. I’ve added this to the expression and the system does display the expression as needed for the 2nd view “drop_route_header_activity” but not the 1st. I’ve tested just using the “drop_site_header_detail_mn” view, listed them separately and used IN() as well but it never picks up that “drop_site_header_detail_mn” view. I’ve also checked the spelling, copy/pasted it but still no luck.

If this is not a good method for restricting the expression, how else could I restrict the expression when restricting other fields in the row isn’t an option?

A million thank you’s!

IF(

AND(
[_THISROW].[record_status]=“Pending”,
OR(
CONTEXT(view)=“drop_site_header_detail_mn”,
CONTEXT(view)=“drop_route_header_activity”
)
)

MAXROW(
“drop_checklist_activity”,
“date_entered”,
AND(
[cms_sites_id]=[_THISROW].[cms_sites_id],
[checklist_item_id]=[_THISROW].[checklist_item_id],
[record_status]=“Pending”
)
)
,
“NA”
)

1 Like
2

I am sure other expert colleagues will share their insights.

I request you to check the below:

There seems to be a comma missing in the expression after the AND() sub expression?

Is the expression evaluating MAXROW() sub expression or “NA” based on AND() result? But that also seems a bit different because MAXROW() will return key.

Also could you update where you are using the expression, meaning column or where else?

1 Like
3

You’re right on the comma, sorry about that I copy pasted and did try initially with the comma in place and no luck.

The MAXROW is returning the highest date_time_entered for the table with the outlined filters and the result of the MAXROW is the key. It functions great but without the IF restrictions, it will run for all records in the table of course and is slowing the app down significantly based on the performance analysis. The expression is a virtual field in the same “drop_checklist_activity” table.

1 Like
4

Thank you. Could you update your exact issue- is inefficient sync time expensive VC the problem or is the expression not running in cettain view as you mentioned?

1 Like
5

In general you’ve got the right idea, yes.

Nesting a “heavy” formula inside a lighter one, where the heavy part only runs if the lighter part evaluates to true, is a solid strategy for handling these sort of formulas.

There are mentions of this here and there in the community; a few people have even commented after they’ve made some of these changes on the significant improvement it’s had.

In fact I just installed a system in an app that checks to see what the status is from a physical column; if this is “whatever” then it runs a much longer algorithmic formula to determine the actual status of the record.

  • But if we’re not “running” or “Active” or whatever, then I don’t want any of that really heavy algorithm to run - I just need it to show the physical status value.
  • In this way, if things aren’t “Active” or whatever, they process really fast; whereas anything that DOES meet the initial criteria, they then take a bit longer.

The net effect is a significant reduction in computations needed to get to the “final state” of your data.

2 Likes
6

I’m trying to implement the view restriction because the Maxrow is
expensive. When I try to insert the view restriction, the expression works
only with the one view and not the other view.

1 Like
7

Please note that CONTEXT() functions may not work reliably in the virtual columns.

CONTEXT() - AppSheet Help

Suvrutt_Gurjar_0-1693712214610.png
Suvrutt_Gurjar_0-1693712214610.png1081×344 48.9 KB

As an alternative approach, I think you may want to create a slice called say “drop_checklist_activity_pending” on the table “drop_checklist_activity” with a filter expression something like [record_status]=“Pending”

Then the expression in the VC could be possibly

IF(

[_THISROW].[record_status]=“Pending”,

MAXROW(
“drop_checklist_activity_pending” ,
“date_entered”,
AND(
[cms_sites_id]=[_THISROW].[cms_sites_id],
[checklist_item_id]=[_THISROW].[checklist_item_id]
)
)
,
“NA”
)

I think currently the MAXROW() expression is running on the entire table “drop_checklist_activity”

With slice, MAXROW() will run only on records having the [record_status]=“Pending”. The IF() expression is anyway running in the VC only on records with [record_status]=“Pending”

Please ensure new expression is returning the desired keys same as those with the previous or existing expression you have.

However the CONTEXT() evaluation part in VC you may need to remove due to the unreliable functioning of CONTEXT() in VCs.

You could evaluate how much sync time the new expression is saving by checking in the performance monitor the previous expression ( running on entire table) and the new expression ( running on slice) . However, in both the cases you may need to remove the CONTEXT() part.

Edit: You could even try to push the MAXROW() expression in the slice “drop_checklist_activity_pending” as filter expression , something like

[Key column]=

MAXROW(
“drop_checklist_activity” ,
“date_entered”,

[record_status]=“Pending”

)

Then the VC expression can be

IF(

[_THISROW].[record_status]=“Pending”,

SELECT(drop_checklist_activity_pending[Key]

AND(
[cms_sites_id]=[_THISROW].[cms_sites_id],
[checklist_item_id]=[_THISROW].[checklist_item_id]
)
)
,
“NA”
)

I think the SELECT() will run on still much lesser number of records because slice has filtered out most records. This will of course need good testing.

2 Likes
8

Thanks Suvrutt. I’ll give it a try.

1 Like