When I’m using the Google Cloud SQL to AppSheet connector, and my data is large—around 360,000 records in a table and growing—AppSheet fails to load the rendering. I plan to use “Views” in GC SQL to filter the data, but my goal is to be able to review all the data. Has anyone found a solution to this problem? From what I’ve seen, AppSheet can render up to 150,000 records in a table, but I’m not entirely sure about that.
I don’t believe there are specific hard limits on row counts from databases. I also don’t believe there is a limitation on rows that can be displayed within an AppSheet view.
I DO believe that you are hitting either:
- An AppSheet processing timeout becausing the loading of the rows is taking so long
- A device side limit on the data size for the web app.
Have you tried looking in the log files for any recorded errors?
I am not sure why you feel a user needs to have access to 360,000 rows of data at the same time but that is HIGHLY unusual. Even large corporations with high performing internal client/server architectures won’t allow that many rows to be sent to any given user - mainly for performance reasons.
You should consider that AppSheet is a web app. It’s architecture is the Internet. The Internet is not conducive to moving large datasets around freely.
If you MUST be able to load 360,000+ rows all at once, then you might need to consider an alternative to AppSheet.
2 Likes
Thanks for the reply. When I say I have, for example, 360,000 records stored in a table, it’s because the data is very old and continues to grow rapidly. My previous problem was that the load time was too long, and I’m already working on fixing that. The issue is that when there’s more than one table in the same situation, synchronization takes even longer. I’ve interacted with the AI, and it suggests using “Custom SQL Queries” to filter the data and retrieve the least amount of information possible from the database to increase synchronization speed, but I haven’t found anything beyond what the AI tells me about that function. Have you seen similar cases?
First, you can’t rely on AI. It gets many things wrong - especially surrounding a specialized platform like AppSheet.
I’m confused…this statement above implies to me that you want to load all the data at once.
Assuming, from your last response, that what you really want is to filter the data as much as possible on the database side to be as efficient as possible - YES that is possible.
When connecting to a database and inserting Security Filters into the AppSheet tables to reduce the number of rows that are loaded, AppSheet will make every effort to create queries that filter the data on the database side.
HOWEVER, it should be noted that not ALL AppSheet expressions can be translated DIRECTLY into SQL queries. What AppSheet does in these cases, is apply what it can in the database query and then applies the rest of the filters in the AppSheet services before downloading to the device. In theory, the dataset is smaller so the services functions run faster.
There ARE steps you can take to ensure the Security Filters are fully translated to database queries. One example is DO NOT use the IN() expression. Instead, if possible, transform it into a series of equality statements - OR( [Column] = “Value 1”, [Column] = “Value 2”, [Column] = “Value 3”).
Not all expressions are as easily transformed AND I am not certain if the inability to transform to database queries is consistent across all datasources.
The BEST way to make sure the Security Filters are as efficient as possible will be through trial and error. For that you will need the ability to inspect the database queries that are executed on the database side from AppSheet. I.e., You’ll need the ability to inspect database logs
I hope this helps!
2 Likes
I understand. One question: is there any way to prevent the data source from being copied by disabling the offline option? From what I can see, it takes a long time to render because it tries to copy the data source. I just want it to work as a viewer.
No. Turning off “offline” mode only delays the download of cloud stored items such as pictures and documents - i.e. with “offline mode” off, those would then only be downloaded when accessed.
AppSheet is a DISTRIBUTED system - meaning that each user gets a full copy of the app and data - pics and docs are considered “objects” that the data “points” to, so are treated slightly different. All data rows will still try to fully download to each device … unless there is filtering in place.
Assuming that the data download is what is slowing things down for your app, the best bet is to control, by using Security Filters, the amount of data that is downloaded at one time. For example, if your data is Date based, then include in your app a Period Start Date and a Period End Date that the user can set to establish which period of time they want to see records for.
Limit the size of this Date range to, maybe 3 months at a time. You can setup these filter Dates in the UserSettings section so they are automatically by user. Then create Security Filters to download only the data in this date range. This will dramatically speed up the app. And the user feels more in control of what they are seeing.
1 Like