Please help or give me ideas.
Initially, there were tables of type Table 1
Table 1 (Price) | |||
Apple | Orange | Pear | |
Market 1 | 100 | 80 | 30 |
Market 2 | 110 | 90 | 45 |
Market 3 | 120 | 100 | 50 |
and Table 2
Table 2 (QTY) | |||
Apple | Orange | Pear | |
Market 1 | 30 | 10 | 40 |
Market 2 | 50 | 15 | 50 |
Market 3 | 60 | 20 | 60 |
But much more bigger(about 20k rows and 100 column)
It was necessary to create permanent filters, I made them through user settings.
User Settings | |
Markets | Market 1, Market 2 |
Types | Apple, Orange |
QTY | 50-150 |
Prices | 80-100 |
And reworked the table in this form.
Flat table for Appsheet | ||||
Market | Type | QTY | Price | Emount |
Market 1 | Apple | 30 | 100 | 3000 |
Market 1 | Orange | 10 | 80 | 800 |
Market 1 | Pear | 30 | 40 | 1200 |
Market 2 | Apple | … | … | … |
Market 2 | Orange | … | … | … |
Market 2 | Pear | … | … | … |
Market 3 | Apple | … | … | … |
Market 3 | Orange | … | … | … |
Market 3 | Pear | … | … | … |
So it became 150k+ rows and app worked slowly. I divided the table into 3 for each market, but still the application was slow.
Today i made a SQL-database with the same format and 150k+ rows and app become even a little more slower.
Is there any ideas how to optimize Google Sheets or SQL (better SQL) to work faster with appsheet.
I have experience with apsheet only with flat tables, it would be ideal that only data that meets the requirements in user settings is uploaded to the application.
Thank you in advance
Please share your security filter expression
I want to make it public when deployed, so i don't use a security filter.
@AleksanderM wrote:
i don't use a security filter
Then, what do you mean by "It was necessary to create permanent filters, I made them through user settings."? If that's not applied via a security filter, then it's not limiting the data being synced.
Assuming your tables are read-only, be sure to enable Server caching.
Simplification often starts by rethinking your requirements and the specifics of your dataset, then comes the next step of thinking how the data could be best modelled. Hence, it is difficult to give you an answer based on an abstraction. But consider for example that you would like to define prices based on quantity and place, you don't have to hardwire all possible cases in a table, but you can for example create a simple calculation table where you write down the rules, then calculate the price dynamically based on users input.
User | Count |
---|---|
44 | |
34 | |
24 | |
23 | |
15 |