Syncing Very Slow for SQL data connections

[PhilTeare]

Since the error for database connections last Friday, the data sync has been running very slowly for our app, which uses a SQL database in Azure.

Here's the thread about last friday's issue:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Bug-Adding-tables-form-MySQL-database/m-p/83705...

I've reached out to appsheet support, they say they are aware and working on the slow syncing problem:

We are working on completely resolving the issue at our end. However, it might be possible that it will be back to normal some time. 

For us, sometimes the sync is just very slow, but more often than not syncing fails, and often gives random error messages too (that are incorrect). I'm wondering if anyone else has been having this issue? Especially with SQL or other external databases?

I'm a touch nervous as we are 4 days in now and there's no clear roadmap for when things will be working again. Would anyone have experience of such happening before, and how long it took before there was a fix?

[Koichi_Tsuji]

Speaking out of my past experiences with AppSheet even before Google time, AppSheet + Azule SQL is not a good combination. When I started with Appsheet back in 2017, I connected AppSheet to Azule SQL. It was ultra slow. I spends years with AppSheet devs to find the solutions, but it was in vain. I migrated my SQL instances from Azule to Google Cloud SQL (MySQL)　

With the same amount/volume of the data before/after , the AppSheet Sync time dramatically changed. Super fast now.

[PhilTeare]

That is very interesting, that you.

Alas in our case the AppSheet app is only one interface. We also have others all based on the same SQL database, so our hands are a little tide. Shame, that sounds an easy fix to speed things up.

Very interesting all the same for future projects.

[Koichi_Tsuji]

Forget Azule SQL with Appsheet. Move to MY SQL with AppSheet. This brings tongs of difference based on my past experiences. Dont trust me and trust what Google says though, as I can not guarantee to you anything.

[Koichi_Tsuji]

My suggestion to you is just to test by migrating (copy/clone) your DB from Azule to google could my SQL.  Make sure to set the proper "index".

I hope you would see some changes and improvement.

[MultiTech]

---

@Koichi_Tsuji wrote:

Google Cloud SQL (MySQL)

---

This too is what I prefer.  Less fiddly bits, less frustration.

[Koichi_Tsuji]

This could be extraordinary.　Google may say, "then go to AppSheet Database". Sorry, it is bullshit and bring us more of troubles... Not sure if or not Google management really understand this situation, but I will leave it untouched.

[PhilTeare]

Any thoughts or experience using Google Cloud SQL for Sql Server?

That could be a solution for us if that might run faster? We could connect to that for our other interfaces so move away from azure. Don't want to make the change though if Microsoft SQL Server is the issue so I'll still have the same slow speeds.

[MultiTech]

This is what I use; works like a charm

Just to give you some insight in an EMR app that I've got:

In an app that has 7 tables like this, plus 19 others of various size (most less than 100 records, a few between 5k - 10k) - massively ref connected inside AppSheet....

• This sync time is largely dependent on how you build your app, just FYI.

[GNF]

I have worked with Google Sheets, Appsheet Database, Supabase, Microsoft SQL Server, Microsoft office 365 and MySQL. In the end, I found that MySQL offers the best performance among them. Based on my experience, I would rank them as follows:: 1-MySQL, 2-Supabase (but connection error up to now), 3-Google Sheets, 4-Appsheet Database (Sometimes connection errors), 5-MS SQL Server,  6-Office 365.

[AleksiAlkio · Accepted solution]

Are you still seeing the same slowness? I have production apps using Azure SQL and I haven't seen such behavior with those apps. One of the biggest app has 50 tables and huge amount of data & functionalities and the sync time in reality is about 4..5 secs.

In general speaking, when using real databases, one of the biggest things to concider are security filters. They need to be set properly so they are converted to a SQL query. When AppSheet is doing this, the AS server will convert your formula as a parameter list which is limited to 999 (The limit is actually coming from MS something like 2000 parameters). You can check how the app has converted the security filter from the Performance Analyzer. It shows something like this..

{"TablePath":"Table=1499152386__[dbo].[Samples]","DataFilter":"RestaurantID Equals Bc6b056ab , Deleted GreaterThan 11/25/2024 00:00:00","RowsRead":"2","ColumnsRead":"13"}

[PhilTeare]

Thanks. Yes I'm still seeing huge slowness, it's completely different to last week.

AppSheet support say they know they have an issue, and are working on a solution, but it's 5 days in now so I'm a bit nervous, we need things back online.

Great to hear it works for you. I'll go follow your pattern of using sercurity filters to reduce data load sizes. I was mostly filtering client side before, so that sounds a much better plan. I'll go make those changes now and will report back on what difference it makes. Thanks.

[AleksiAlkio]

Please remember to use proper expression when adding security filters with database tables. https://support.google.com/appsheet/answer/10104706?sjid=17181202457766541764-EU#security-filters-wi...

This is one simple example how you can write the formula with these limits..

AND(
IN(USEREMAIL(),SELECT(AppUsersForRestaurant[Email],[Role]="Admin")),
IN(TRUE,SELECT(Restaurants[ValidAdmin_MaterialLibrary],[ID]=[_THISUSER].[Restaurant])),
IN("MaterialLibrary",Subscriptions[Item])
)

[Koichi_Tsuji]

---

@AleksiAlkio wrote:

AND(
IN(USEREMAIL(),SELECT(AppUsersForRestaurant[Email],[Role]="Admin")),
IN(TRUE,SELECT(Restaurants[ValidAdmin_MaterialLibrary],[ID]=[_THISUSER].[Restaurant])),
IN("MaterialLibrary",Subscriptions[Item])
)

---

IN() IS ONE OF THE HEAVY OPERATION WITH APPSHEET.....  IN() X 3 COULD BE DEADLY HEAVY, I SUSPECT

[AleksiAlkio]

If that size of app syncs within 4..5 seconds, does it sound it's too complex 🙂

EDIT: And the app I'm referencing, is built in a way that other tables are secure filtered with much simpler formulas so multiple IN() would not need to read through big tables.

[PhilTeare]

security filters that properly convert to SQL query was the most effective thing. We are back running again.

Interestingly, security filters that do not convert to an SQL query did not help, and in some cases made things worse. In that case all rows are loaded to AppSheet's backend, where the filtering would happen, then the filtered set would be sent over the wire to the front end. AppSheet back end seems to be the slow part, so in some cases it was faster to send all records to the front end than to filter on the backend.

Thanks again everyone, hugely appreciated.