A security filter is an expression that is evaluated on each row in a table and returns true or false. If true, the row is included in the app and if false, the row is not included. While security filters are primarily meant to be a security feature, they can also be used as a scalability feature.
Security filters usually compare data from the row with some user-specific data (e.g., the user's email or some user setting value). For example, the app user's email address might be used to retrieve only the records that have that email value in a specific column.
Security Filters with Spreadsheet Data
When reading data from a spreadsheet source, the entire spreadsheet or worksheet is retrieved from the cloud backend before the security filter is applied. Therefore, this does not reduce the time needed for this initial step of the sync process. However, it can significantly reduce the time needed for the second step (sending the data to the app) and can significantly reduce the time and space needed for the third step (saving the data locally with the app).
Security Filters with Database Data
When security filters are used with database sources, there is the potential for the system to convert the security filter into a database query. To explain, if the underlying database table has a million records but the security filter limits the table to those where the value of the Email column is "firstname.lastname@example.org", there are two ways this can be executed:
Fetch all the million rows from the database to the AppSheet backend, evaluate the filter and retain just the ten records that match, or
Send the database a query: SELECT * from MyTable where Email = 'email@example.com'. The database would return just the ten records that match.
Obviously, the second approach is much more efficient. Database systems have techniques like indexing that can run such queries quickly. AppSheet can scale to handle millions of database records, provided the security filters can be mapped to efficient queries.
The best way to ensure that a security filter is efficient is if it is a simple equality comparison of a column to a value, or if it involves an AND() with two or more equality comparisons. More complex conditions are almost certain to lead to inefficiency as the data set scales.
We attempt to include the security filter in the SQL query for the following database providers:
Microsoft SQL Server