LinkedIn
Twitter
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?
You're dereferencing _THISROW unnecessarily:
Your use of INTERSECT() could be written more concisely with IN():
Is the same as:
IN([TWUAOrders_fk], TWUAOrders[TWUA_id]),
It could also be expressed with a more efficient expression:
ISNOTBLANK([TWUAOrders_fk].[_ROWNUMBER]),
Beyond that, your security filters will never be quick so long as you use nested queries in them.
@Markus_Malessa wrote:
Is there some way of prioritizing table loading and then first executing security filters before calculating VCs, or does AppSheet automatically do this?
It's already done.
It would be helpful to know what you're trying to accomplish with the security filter. What data do you want loaded (or not loaded)?
@Steve as always I appreciate your input on these complex issues. I replaced the section of the expression that you suggested with both suggestions, each time syncing the application with the 'problem' user account as the 'Preview' user. For both of your suggestions I consistently received the timeout error. I then replaced that section again with what I previously had and synced multiple times, with even my previous expression giving me a timeout error about half the time. I then replaced that section again with a more intensive comparison and got more consistent results again with the sacrifice of long sync times.
I have copied a schematic of my general setup. Note that pretty much everything is dependent on relationships between tables being stored in bridge tables (many-to-many), so accordingly most of the filter expressions contain references to the bridge tables.
The structure of the app for security filters is first load a user record, then filter TWUAOrders by relationship between ReporterInfo and TWUAOrders stored in TWUAOrders_Has_ReporterInfo1, then filter PermitList by relationship between PermitList and ReporterInfo stored in ReporterInfo_Has_PermitList and also by relationship between PermitList and TWUAOrders stored in TWUAOrders_HAS_PermitList. Then WaterUseData is supposed to be filtered by matching records in PermitList(filtered) via WaterUseDataPermits_HAS_Permits and records that are linked to TWUAOrders via a column [TWUAOrders_fk].
The WaterUseData table is the only table that will get filtered on depencies of PermitList and TWUAOrders. However I have 6 other tables in the app that also get filtered depending on results in the PermitList table via bridge tables as well. I know it gets rather complicated, but my data structure doesn't allow for one-to-many relationships with the exception of a very few cases, in my opinion.
Thanks for still looking at this @Steve. Ultimately I am hoping for an expression in the WaterUseData security filter that will significantly reduce sync time.
Having used both IN([TWUAOrders_fk], TWUAOrders[id]) in spot 1 and IN([PermitList_fk], PermitList[id]) in spot 2 I generally received a timeout error through AppSheet indicating long load times specifically within this table.
I can only think that using table references in the security filter and those tables themselves having complex security filters results in a cross table reference issue causing the timeout error.
That leaves me to using the current filter along with the possibility that I am simply not able to make any further improvements and having to deal with the long sync times as you already mentioned in a prior comment. I did try replacing the ISNOTBLANK(INTERSECT()) statements with your suggestion of the simpler IN() however this seemed to revert back to the timeout issue due to the cross table reference. I appreciate you taking a look at this, but I think in the end I am having to realize that the security filter complexity might not make AppSheet the ideal solution for this particular app.
