Scaling Woes

[RedVox]

I have an app that's now too big and running into sync time-out errors.   Unfortunately, the app has a single user so no easy way to scale it.  I think I will remove all the one-year statistical views so I can limit the data to the past 40 days or so and will move the stats portion into looker studio.  The app is using an appsheet database as the source.  Any thoughts on whether a move to MySQL would eliminate the problem?  Here's the deets on my current data:

Clients - 175 records - 4 relationships -  6 calculated fields - 37 columns
Family Members - 200 records - 1 relationship - 3 calculated fields - 11 columns
Food Orders - 550 records - 1 relationship - 8 calculated fields - 33 columns
Drives - 250 records - 1 reference - 0 calculated fields - 19 columns
Food Manifests - 75 records - 2 relationships - 15 calculated fields - 25 columns

Thanks

[Kyric]

I think the issue is that there is some sort of sync issues / delays going on today, you can see other people posting the same.

The above tables seem fine in size. Someone tell me if I'm wrong, but I think you will actually get better performance from appsheet database than MySQL because they designed them specifically with appsheet in mind. That said, the size of your data currently shouldn't be an issue at all - my MySQL app is about 5x-10x the size of what you're working with and it's working fine, up until today that is.

[RedVox]

Thank Kyric.  Yes, now that you mention it, I have other apps that are not opening today.  My other apps use MySQL tables but the problem looks identical.  Yeesh!  Looks like a pretty big problem!   

[juandres]

No es la cantidad de registros, mira, tengo una app con con todas las tablas en mySQL menos la tabla que aloja el menú, acabo de migrar esa ultima tabla a mySQL y la aplicacion me corre perfecto. En el resto de mis apps tengo varias tablas alojadas en Google y las mismas no se sincronizan. Pienso que lo resolveran en unas horas

[juandres]

la primera es con bd mysql, y la segunda Google+mySQL

[MultiTech]

Mate if you're having problems with your app, and you haven't even broken 10,000 records in one table.... you might need to re-address how things are built.

How many SELECT() formulas do you have in virtual columns, action conditions, formatting rules, show-if formulas?

• Maybe some of the other community ninjas will chime in here with some links to previous replies that nailed this answer down before.  I know there's like 1 really good answer out there...

--------------------------------------------------------------------------

But with the numbers you're talking, you shouldn't be having a problem with sync times.  It's got to be some heavy lift formula or something - and these can be corrected, giving your app a new life.

--------------------------------------------------------------------------

PS: I would migrate away from databases; it's a prototype that's still in development.  Google sheets is the place to be for data. 😉 

Drive + Sheets + AppSheet + Apps Script = .....

[RedVox]

Update: So, I put in a support ticket yesterday, and about 12 hours later, I got a note saying they've fixed the problem.  What problem they fixed, whether it was an outage effecting others, etc, they did not mention at all. 

Now, my app is taking 30 seconds to sync.  My table with 200ish records has 8 calculated fields with formulas similar to this one: 

Count(Select(
      [Related Food Orders][Bread],
      In("White Bread", [Bread])
))

I don't have any conditional formatting.  Do you think these 8 formulas are too much?

[Kyric]

Best way to dig a little deeper is to use the Performance analyzer under Manage > Monitor > Performance Profile.
It should show your sync times and you can click on the binoculars icon to drill down and see specifically which tables and calculated columns are taking the longest.
You may have to mess with the settings a bit in the performance details in order to really pinpoint it (unchecking "focus on slow steps" will show how long every table and every virtual column takes to load).
That will hopefully highlight which calculations need to be optimized or rethought.

[MultiTech]

---

@RedVox wrote:

Count(Select(
      [Related Food Orders][Bread],
      In("White Bread", [Bread])
))

I don't have any conditional formatting.  Do you think these 8 formulas are too much?

---

Formulas like this are relatively efficient, I wouldn't think they would be causing that much of a slow-down; but maybe they are.

• Nice thing about AppSheet is there's 4 ways to do any 1 thing, so perhaps the solution is some reconfiguration of your system.

What is it you're trying to do?

• It seems like you're trying to get something specific to a Type of something...
  • Instead of "hard-coding" something like this, it's typically best to find a way to add a new table that would hold this information and reference connect everything together.
  • Then you could utilize INTERSECT() in some very clever and complex ways.

[RedVox]

It looks like the AppSheet servers are behaving better now, which relieves
some of my problem. But I did do some things to speed things up myself. I
realized that most of my calculated fields are intended to capture info for
statistical purposes and are not needed at the moments when these tables
are being used. So, I made the fields non-virtual and put the calcs in a
bot. Now all the calculations take place behind the scenes and only happen
once per record. It's made a big difference in the sync times.