Very slow sync speeds and frequent timeout errors

[YogeshLaddha]

Hello,

I have been facing very slow sync speeds and timeout errors like "Unable to fetch app definition. Error:Request cancelled: took too long to process: Computing a formula/n" from last couple of days.

Our apps have been working fine for a long time and all of sudden since last couple of days, we started getting errors like the one mentioned above.

On checking the performance analysis, I found out that most of the time is taken for computing virtual columns and reading tables. which is understandable as we gather large amount of data everyday, most of which is stored in sql db.
However, now it seems like the computation of virtual columns is taking much more time than before without any increase in amount of data or any changes or addition of Virtual columns.
Also, "Data type conversion" takes around 40-50 seconds, which I think is very high.

Can you please assist and help me understand why this is happening since last couple of days?

Thanks for your help.
@Phil @Steve @Aleksi 

[Aleksi]

Hi, more data you have in your data source, more time it takes to calculate.. and this is happening every time when the data is synced. You mentioned that you save a large amount of data every day which means your virtual columns need more and more time to do that. if you can't change those virtual columns or write them in a better way, then you need to think what kind of security filter would reduce your data amount. If you use a simple expression your database is able to understand, those security filters are then converted as queries. Then the filtering would happen on your database before the data is sent to AppSheet server.  

[YogeshLaddha]

Thank you for your response Aleksi.

I am aware about all the measures to take to improve the sync speeds.
We have reduced the virtual columns as much as possible. 

Regarding security filters, we have applied them on almost all of our tables which have large amounts of data such that each user loads only the data that he needs.
I understand simplifying the expression would convert them as queries and filter the data at database level.
However, there are certain cases where we need to give users the flexibility to choose certain time periods for which they want to see the data.
I have done this using USERSETTINGS, where i have a From and To date fields.
I am using those fields to allow users to change the time period and update the security filters to load data for that time. I guess this is not simple and appsheet has to fetch all data and then the filter is applied.

This is one of my security filter expressions : 
"
IF(
ISBLANK(USERSETTINGS(From)),
Date([Timestamp]) >= TODAY()-30,

AND(Date([Timestamp]) >= USERSETTINGS(From) , Date([Timestamp]) < USERSETTINGS(To)))
"
Do you any suggestion on improving this? Or is it better to then directly load data for last 3 months, rather than allowing user to choose? I think 3 months should be enough for us in most cases.
Do you think using expression like "Date([Timestamp]) >= TODAY()-90" would be better than the one above?

Also, what do you think about the "Data type conversion" that takes around 40-50 seconds. It is not normal, right?

Thanks for all your help.
Really appreciate it.

---

@YogeshLaddha wrote:

Hello,

I have been facing very slow sync speeds and timeout errors like "Unable to fetch app definition. Error:Request cancelled: took too long to process: Computing a formula/n" from last couple of days.

Our apps have been working fine for a long time and all of sudden since last couple of days, we started getting errors like the one mentioned above.

On checking the performance analysis, I found out that most of the time is taken for computing virtual columns and reading tables. which is understandable as we gather large amount of data everyday, most of which is stored in sql db.
However, now it seems like the computation of virtual columns is taking much more time than before without any increase in amount of data or any changes or addition of Virtual columns.
Also, "Data type conversion" takes around 40-50 seconds, which I think is very high.

Can you please assist and help me understand why this is happening since last couple of days?

Thanks for your help.
@Phil @Steve @Aleksi 

---

[KaryZhang]

It seems much quicker today.

[KaryZhang]

But still slow than before.

[Aleksi]

Try to use something like..

OR(
Date([Timestamp]) >= TODAY()-30,AND(Date([Timestamp]) >= USERSETTINGS(From) , Date([Timestamp]) < USERSETTINGS(To)))