Scale using security filters

If you are loading large amounts of data your app will be less performant and is more likely to experience "out of memory" errors. A security filter can reduce the amount of data that AppSheet loads on your behalf.

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 (such as, 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 versus table slices

As covered in the following video, a security filter functions differently than a table slice. Slices download all of the data to the client before filtering the data. Security filters retrieve only the records that meet the expression criteria, which restricts the content that gets downloaded to the client.

Quick Tip Friday - Security Filters

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 [e-mail], 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 =[e-mail]
  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.

AppSheet attempts to "push" the security filter into the SQL query for the following database providers:

• MySql
• Microsoft SQL Server
• MariaDB
• Oracle
• Postgres
• Redshift

Efficient security filter definitions for database data

The best way to ensure that a security filter is efficient is if it reduces to a simple comparison of a column to a value, or if it involves an AND() with two or more such comparisons. More complex conditions are almost certain to lead to inefficiency as the data set scales.

Here is the list of simple conditions that AppSheet does recognize and push into the SQL query:

• [Column] = constant-value
• [Column] < constant-value
• [Column] > constant-value
• IN([Column], {constant-value1, constant-value2, ... constant-valueN})
• AND(simple-condition1, simple-condition2, ... simple-conditionN)

Almost all other conditions are not pushed into the SQL queries (such as NOT() or OR() expressions). The platform does make an effort to push partial conditions. For example:

• AND([Column1] = 5, LEN(CONCATENATE([Column2], " file")) < 20) - The second condition is not one of the simple conditions due to the LEN(CONCATENATE([Column2], " file")) part of the expression, but the first is, so the first alone will be pushed into the SQL query.

It is important to understand that often, the expression itself may be more complex, but what matters is that it can be reduced to a simple form just before execution. For example:

• AND([Column] = 5, USERROLE() = "Admin") - At runtime, AppSheet knows the user role. If the user is an admin and [Column] = 5, this expression evaluates to TRUE (which means "fetch all the rows").

• IN([Column], SELECT(AnotherTable[SomeColumn], [EmailColumn] = USEREMAIL())) - The entire SELECT() function evaluates to a constant list at runtime before this security filter has to be run. Therefore, the whole expression reduces to IN([Column], list-of-values) which can be pushed into the SQL query.

In general, for efficiency, avoid complex NOT() and OR() expressions in the security filter. If there are complex expressions, they should avoid using columns of the table that is being fetched.