Optimization on Security Filters

[Markus_Malessa]

In trying to optimize some security filters I started running into some issues loading 'dependent' tables that would be filtered on results contained in another table that also has some security filters built in. Most of my tables with security filters are MySQL tables. The error:

The  security filter on one of my tables called WaterUseData that would take the longest to load, contains about 55,000 records total and has the highest dependency on data loaded into other tables, is as follows:

OR(
  [Created_By] = USEREMAIL(),
  ISNOTBLANK(INTERSECT(LIST([_THISROW].[TWUAOrders_fk]), TWUAOrders[TWUA_id])),
  ISNOTBLANK(INTERSECT(LIST([_THISROW]),
    SELECT(WaterUseDataPermits_HAS_Permits[WaterUseDataPermits_fk],
      IN([Permits_fk],
        PermitList[id]))))
)

This is the security filter that would give me a timeout error on one specific user only so far. I suspect, this is because the security filter on the table PermitList would also take some time to execute and for this particular user would return 52 records out of approximately 2,500 stored in the table.

I changed the security filter to the following instead:

OR(
  [Created_By] = USEREMAIL(),
  ISNOTBLANK(INTERSECT(LIST([_THISROW].[TWUAOrders_fk]), TWUAOrders[TWUA_id])),
  ISNOTBLANK(INTERSECT(LIST([_THISROW]),
    SELECT(WaterUseDataPermits_HAS_Permits[WaterUseDataPermits_fk],
      IN([Permits_fk],
        SELECT(ReporterInfo_HAS_PermitList[PermitList_fk], ANY(ReporterInfo[id]) = [ReporterInfo_fk])))))
)

Note that in this case the dependency on PermitList was replaced with a dependency on ReporterInfo instead, but the security filter on ReporterInfo is extremely simple, filtering by an email column that matches the logged in user.

This change did not result in the timeout error, however sync times are still extreme clocking in around 90 seconds for the particular user.

At this time I am mostly just hoping for suggestions from the community how to possibly improve this type of situation? Is there some way of prioritizing table loading and then first executing security filters before calculating VCs, or does AppSheet automatically do this? I understand with the way that AppSheet is set up I am bound to using their functions for security filters, which is somewhat limiting since MySQL queries would be able to execute these complex filters within about 2-3 seconds for existing data. I enhanced my tables already to include Created_By columns for each table where new data is allowed to be added, however I still need users to have read access to specific records that might have been created by someone else. My only other solution that has crossed my mind is add a column for Allow_Access_To and treat it as a list of emails, in that case if this column is an actual column vs a VC then there would be a lot of Bot/Action dependency to give permission on a lot of related records in possibly 12 other tables if I change the permission settings in one table.

Any ways how have others handled these aspects?