Hi Phil - san,
Thank you very much for your detailed explanations, I m now fully cathin up wiht you I hope. I will create new appsheet account for testing purpose, and will get back to you separatetly to conduct testing. Yes, all my production app is running on MS SQL as data source, better to avoid to conduct testing on the same account where we may see unexpected behaviro which may interfere the use of prouction apps.
The general performance improvement is surely good news for users like me, hosting data on SQL instead of Sheet.
For now, I came up with two questions I hope I can get your advice over.
- SQL index
It is IMPORTANT that you have a SQL index on the key fields to make the SQL SELECT WHERE query as efficient as possible for SQL to execute.
To improve the performance in general, I will take the suggetion to make key fields as index. I just wondered if I should make the other fields which could be employed afte WHERE clause on SQL select statement. For instance, I do have date fieds, and on appsheet expression i filter out the older data whose date filds is older than X date. In that case, I assumed it will improve the query performance (both on SQL and Appsheet engine) by making “date” fields as idex as well as key?
Or doing so will not affect any performance at the end?
- Possible workaround to make the performance far better.
I understand the technical mechanism in terms of how the rows are added and updated by API , CSV import. So all in all, the update/add operation against the table with “smaller amount of exisitng rows” should perform better rather than table with tons of existing table.
To improveperformance better, I thought there is possible workarond like this
a. First, on the SQL server, create a VIEW out of table we wish to add new rows by API/CSV file.
b. This view is made out of table with filter condition, where the matched rows is marginally zero. For instance, filtering by date filter, with condition, date <~ 2100/1/1 something like that, and returning zero row as we dont have existing rows mathing such condition.
c. Read proper table as well as this VIEW to Appsheet.
d. Create action for CSV file import, but this action is associated to VIEW instead of Table.
Whenever the import csv file is fired, then the row will added to VIEW instead of table, where the existing rows are basically ZERO all the time, and which will make the performance far better? The uploaded rows to this VIEW will be ruled out after import opration as it does not matching the condiitions.
If this works, then in case of “adding new rows” ops will perform better? although it wil cause the problem when we UPDATE the rows though.