I’m currently working with support on this issue but wanted to see if it was plaguing anyone else.
It seems since 11/7 that weird glitches have happened all around (Automated Emails not working, OData connection issues, performance logs unavailable, account blocking, etc. (like the OData problem, mine went away later on 11/7 but then was back on 11/11 and now persists)
I’m having 5-10x sync and edit delays (old syncs and edits were around 4-6 secs - now around 25-40 secs)
Now that performance logs are back up, all of the delay seems to be coming from “Data Type Conversion”. I’ve never noticed this as a contributor before when optimizing performance, so this seems like a new issue. (images attached - edits on tables with related data can stack these conversion on top of each other furthering the delay)
I haven’t had any significant changes to the app in this period and the rest of the logs look within expectation.
Is anyone else seeing this in their performance logs?
I’m trying to confirm this isn’t something on my end - I use a MySQL database connection hosted on Google Cloud SQL. I also use some Appsheet Databases for smaller tables because it was convenient (although these have had issues in the past)
I use Big Query. I’d show logs but they have information I can’t include here.
I have one app that filters down to the specific user and see NO Data Type Conversion type entries. Syncs are very quick.
In a second app, it pulls in data for about a 90 day period across all users. I am seeing Data Type conversion entries but the most expensive one is 11 secs on a table reading 8600 rows and 220+ columns and that is on a Virtual Column calculating “Related” items.
Keep in mind that parallel processing play a big part in overall Sync times. You can find the Degrees of Parallelism at the top of the Performance Log entry
I do suspect that the Data Type Conversions can vary from database to database and may depend on what types you utilize in the SQL database. You want to keep types as plain as possible in the database. Of course if you use the database as a source to other platforms, you may have no choice.
As an example, for all Text, LongText, Enum, and any Lists, I use only the String data type in Big Query.
Yeah there may be a few columns I could shore up - I’ll look at that - but for the most part all of my types are pretty consistent and well-matched for database vs Appsheet.
The biggest question mark is that this is a very dramatic increase in sync time, and I haven’t really changed anything lately (amount of VCs and data are basically the same)
Also weird that it went back to normal for a few days between 11/8-11/10
Just seems too coincidental with all of the other issues happening now.
If you are running from a desktop, you might want to try clearing browser cache.
I will go back in the log history to see if there has been a performance increase int he larger app. it has a long Sync time so we don’t really monitor it. Users are able to use the app all day without the need to Sync so they don’t see it really.
Yeah unfortunately affecting both desktop and mobile clients. I just realized I was able to go back and look at some older data in October (10/20) in the performance logs. You can see in this image that the data reads are roughly similar in size, but data type conversion is under 1 second. Not sure what’s going on.
Just comparing a single day at the start of the month to yesterday, I am seeing an increase in Sync times from an average of 46 secs to 54 secs. I have made no changes that would account for this increase and data volumes may be slightly higher.
So it does seem there has been some impact on Sync times. It needs more comprehensive testing to say with certainty.
Having the same issue here. Sync times slowed and now time out when using AlloyDB (Postgresql) as our backend for some tables. This was never an issue before roughly the same time you mentioned it starting and the database itself is way over provisioned for the load and shows most wait time stuck in sending data to the client (AppSheet servers).
I just did some extra testing support wanted me to do. Copied app and copied data source (copied a new instance of Cloud SQL). Performance still 3x slower on this as well. It does seem like it has something to do with the cloud database connection maybe?
I have another app that only uses Appsheet database (although this is just a test app with almost no data) and its still super quick
Yes support keeps telling me that I have a good bit of virtual columns (most of these are derefence though).
Before this issue started my performance for an app sync was based off the virtual column time in this screenshot (largest table) - In this case it was 8 seconds.
The database read and conversion was always 1-2 seconds or less. Now its anywhere from 15-30 seconds (basically overnight with no substantial structure changes or data changes)
If this was something I messed up it would be nice to be able to dive into this Data Conversion and see what is actually contributing to it, to @Landan_QREW ‘s point, so I could attempt to troubleshoot it.
“Mid” is probably something like message ID that should map to some text - best way to look at this is probably just to use the UI but uncheck all the boxes at the top (I think that gives the most granular view).
Calling this “Data type conversion” in the performance logs is maybe understating what’s happening in this part of the sync - that’s referring to converting the raw data from the data source into in-memory representations that the server can work with (e.g. “DateTime” objects to represent dates). But that conversion is happening in the context of copying all the data from the DB table. This will necessarily scale with the data set size in a way that makes it difficult to add more granular performance logs to see how exactly that time is distributed across the table rows and columns (making the logging too granular here could actually make the performance worse).
One thing I think we can infer from this though is that virtual columns should not be a factor, since this step is about reading the data from the provider before any VC calculations have started (those should show up elsewhere in the performance logs).
For the data in question, are there any columns with particularly large or complex values (e.g. EnumLists with a lot of items selected)?
Hey Adam thanks for the response and info. I don’t have any columns with large values. There’s a few columns I used CONCATENATE a good bit to fit more data on mobile versions, but still nothing crazy.
The main issue for me is that my tables and data structure have remained largely the same (I’ve been using Appsheet for 2+ years and have maintained similar performance to the first screenshot with security filters and adjusting VCs to be efficient)
My screenshots attached show this 3-4x lag increase that basically happened overnight somewhere around 11.7.25
Performance Logs were down around 11.8 to 11.18 (coincidentally?) so I’m missing some info - but this is the main difference pictured here. I don’t recall adding any VCs or making any app changes during this time period.
I’ve also attached two screenshots of spreadsheets showing an Edit Operation on the same table - one for 11.5 (6 seconds) and one for 11.19 (25 seconds)
(I see this Warehouse Destination VC probably needs to be optimized some, but going from 1.5 secs to almost 10 secs is rough)
Sincere thanks for your reply. Genuinely appreciate an engineer taking a look into this.
In my table, there are quite a few columns; although most are super basic. Text, Yes/No, Longtext, Enum, etc. There are a few standout columns though:
4 EnumList columns (rows would not have more than 3-4 values selected per column though)
2 LatLong columns
3 Drawing columns
1 HTML type longtext column
A couple follow up questions I have:
Are you aware of specific column types that are take longer to compute (you mentioned enumList but are there any others)?
How do ref base-type enum and enumList columns affect this “Data type conversion” step?
Would it be more performant to normalize into child records and add a table (rather than use enumList columns)?
Do you know if AppSheet has any intention of introducing “Lazy / Paginated Loading” to reduce computational load on sync? Realistically, there isn’t more optimization we can do in terms of reducing row count for the user (not just on this app, but for many of our client’s apps) unless syncs could happen completely in the background (which I think would require lazy loading), so we are really bound to AppSheet’s scale boundaries with these sorts of things.
Thanks again for your consideration of this issue.
I did some profiling and I believe I found the reason for the increase in latency. I’m working on some optimizations now that I expect should bring it back down to prior baseline or better.
I haven’t done an exhaustive profiling of each type, but it appears that the recently increased latency is mainly related to temporal types (Date, Time, DateTime). The change I’m working on now should bring those back down closer to other types, but they will likely still be somewhat more expensive than other types due to the need to support parsing multiple date formats as well as allocating DateTime objects. EnumList I suspect would be another that’s expensive due to splitting of strings and allocation of lists, even if the number of values is small (but would also get more expensive as the number of values grows). For most other types I don’t see anything that looks particularly expensive. Image/File/Drawing columns generally just store the path to the file, and it doesn’t appear that resolving the file data is part of the “Data type conversion” step. LongText and LatLong should just pass through unmodified.
Ref and Enum-Ref should be similar to whatever the base/referenced type is. EnumList I expect will be generally more expensive for the reasons above, but more so if the base type is something like Date since each individual value after splitting the list will also go through its own data type conversion.
Hard to say without comparing specific cases. Keep in mind adding a table means one more table that will go through this data type conversion. I don’t think I can advise one way or another, other than to try different approaches (e.g. in a test copy of the app) and see if you find a difference.
We have wanted to do this for awhile and done some initial work in that direction, but it has been hampered by difficulties with expression support that we still need to work through. In the meantime we have tried to move syncs to the background as much as possible so the latency is less noticeable (in Desktop and New Mobile Framework this is the default) and introduced mechanisms like Quick Sync to make the app less reliant on a full sync to pick up changes. I hope we’ll be able to do lazy loading eventually, but the feasibility is still uncertain.
