Advanced Techniques: Horizontal Scaling

Stefan_QREW · 08-30-2019 10:08 AM

Super cool concept and approach to data size management in this article : https://help.appsheet.com/en/articles/3086424-advanced-techniques-horizontal-scaling

Question:
Does this effect only apply to SQL type data stores? You mention about partitioning when using spreadsheets, so do you have a method for applying this technique using data partitioning instead of security filters?

@Grant_Stead you may also enjoy this answer

Derek3

Hi @Stefan_Quartemont,

As described in this article, there are two primary steps during the sync: 1. The AppSheet server reads the data source; 2. The AppSheet server sends data to the device.

When applying a security filter against a SQL database table, you get a performance benefit for both steps 1 and 2.
When applying partitioning against a spreadsheet datasource, you get a performance benefit for both steps 1 and 2.
However, when applying a security filter against a spreadsheet datasource, you get a performance benefit for step 2 only.

With that in mind, you have a couple options for applying the horizontal scaling technique when using partitions. Each option comes with a trade-off.

Option 1:

Partition the child table using the same criteria as the parent table. One way to do this is as follows:

In the parent table, there are two important columns: the primary-key column and the partition column. Let’s assume these are labeled “Parent ID” and “Parent Partition”, respectively.
In the child table, there are three important columns: the primary-key column, the partition column, and the reference column (pointing to the parent). Let’s assume these are labeled “Child ID”, “Child Partition”, and “Parent Reference”, respectively.
For this scenario, you want records in the child table to be partitioned the same as records in the parent table. So you can apply a formula to the “Child Partition” column: [Parent ID].[Parent Partition]. When you create a new record in the child table, the “Child Partition” column will automatically be assigned the same partition logic as the parent
Turn partitioning on for the parent table. The Partition Expression for the parent table would generally be based on the USERSETTINGS(). For example: USERSETTINGS(Partition)
Turn partitioning on for the child table. The Partition Expression for the child table should point to the “Child Partition” column. This results in the simple expression: [Child Partition]

Outcome:

AppSheet will only load records of the parent table and child table that match the partition criteria.

Benefit:

You will get a performance benefit in both Steps 1 & 2 as described at above.

Trade off:

It is difficult to reassign the child record to a different parent if the new parent is in a different partition. Also, there is additional effort to setup partitioning for the child table. This can be significant if there are several child tables.

Option 2:

Setup a security filter on the child table to only load records where the value in the “Parent Reference” column matches one of the values in the “Parent ID” column. This is the very similar to the process described in the horizontal scaling article. The security filter for the child table would be: IN([Parent Reference], Parent Table Name[Parent ID]). With this approach, no partitioning is applied to the child table.

Outcome:

AppSheet will read the entire Child table, but it will only send records that match the security filter to the device.

Benefit:

Partitions only need to be applied to the parent table. The security filter for child records will automatically adapt to show only the child records associated with the parent partition. This is especially helpful if you have a lot of child tables.

Trade off:

For the child tables, you only get a performance benefit in Step 2. This will result in a much smaller overall performance benefit for the child tables.