Concurrent User Submissions

zchichak · 10-22-2021 12:31 PM

Hello there,
Looking to better understand the functionality of how AppSheet handles high volumes of concurrent users. I have reviewed the documentation and it looks as though the concerns about multiple users only comes into play when they are editing the same row of data.
If I have ~1500 individuals needing to submit a form(within an 8 hour period), and the table has the “key” with UNIQUEID(“UUID”), am I going to run into any issues? Aside from long sync times?

Steve

I don’t have experience with that volume, but conceptually, I wouldn’t be worried.

Perhaps some other regulars have experience at this level? @MultiTech_Visions @Grant_Stead @Koichi_Tsuji @WillowMobileSystems @Marc_Dillon

Koichi_Tsuji

In case of Google spreadsheet for data source , lower performance is expected technically speaking. If multiple concurrent users updates data, Appsheet deal with such request one by one, first in first serve basis.

Meaning let’s assume 10 users update something exactly same time, on second level, then Appsheet to start to deal with request which come first. Then rest of 9 other queries will queue up. If one operation take 10 Sec to complete the last person need to wait for 90 sec to be served.

This is because Appsheet will lock the spreadsheet to other query while one request is attended. If request volume is high , potentially you may encounter timeout issue.

Koichi_Tsuji

So far it is always better to host data on sql rather than spreadsheet to ensure better performance, as sql is working on row level lock as far as I’m correct.

@praveen

WillowMobileSys

In addition to what’s already been said…be aware that if you are using Google Sheets (and likely any Cloud datasource), there are limitations on the number of sheet queries over a period of time. I don’t recall the parameters and they may have changed.

A “normal” AppSheet app uses the App Creator account to house and interact with the sheet. So all sheet activity goes through this single account. If your sheets are implemented heavily with formulas, you could hit this query limit fairly quickly.

I experienced this problem when I first started out. I created an app where users were expected to pre-assign rows to themselves for an event. I would have them all (about 12 people) sit together and go through the pre-assignment. They would hit the query limit and have to wait for a few minutes to try again. I refactored the app to be more efficient and removed ALL sheet formulas and never had the problem again.

Bottom line - don’t rely on sheet formulas to do the work for you. In fact, I always strongly recommend to NEVER use formulas in the datasource sheets. Especially if you have intentions of eventually moving the app to use a relational (SQL) database. Trying to refactor sheet formulas into the database can be very tedious work. If you always have a formula-free sheet, then moving to a database is as simple as lift and shift of the data and few minor tweaks of the database column types.

WillowMobileSys

Have you by chance been able to formally test this theory out?

I often wonder about this. I did at one point have an app on a sheet and a copy on a database. After several days of playing with the two, I couldn’t discern any performance differences. It was all rudimentary testing. It would be better if I could setup a test harness of some sort to provide accurate testing against the different datasources and data volumes.

Koichi_Tsuji

This is something like when we go to clinic, then you need to wait for other patients to be served first as doctor can see pacient at one time. If queqe is long, waiting time is accordingly gets longer.

Mr. Bean could not wait patiently, then in other words, if this happens to AppSheet, we will see “TIMEOUT” and data change will fail, but this is extreamely rear case, I would call.

Grant_Stead

Yeah, I don’t think you’ll see any issues.

zchichak

Thanks for all the responses. I have built many applications in the past but never had to worry about these items.

I am using sheets as a data source, but the tables are specifically created without formulas in efforts to increase efficiency.

All calculations and data transformations are done by AppSheet rather than Sheets.

I hope Sheets parameters will allow this level of activity. “500 requests per 100 seconds”. I think this will be an acceptable amount for my use case; however, I hope these parameters are per sheet rather than per user, as I have 4 tables that are updated from a single submission, but I have split them over 4 workbooks. If it is per user, each submission could potentially count as 4 requests.

There may be a chance that my organization has increased usage amounts through our enterprise agreement with Google.

The application is being released on Monday - I’ll report back.

WillowMobileSys

These are the limitations I have found as of 6/15/2021:

This version of the Google Sheets API has a limit of 500 requests per
100 seconds per project, and 100 requests per 100 seconds per user. 
Limits for reads and writes are tracked separately. 
There is no daily usage limit.

I am not certain what the definition of a project is. I would assume it’s the collection of tabs in a single Sheets file.

zchichak

I am not certain what the definition of a project is. I would assume it’s the collection of tabs in a single Sheets file.

For me, I am most worried about the “100 requests per 100 seconds per user” especially due to the application running from the creator’s account. I am not sure if these parameters apply to the way AppSheet interacts with the Sheets’ API. Moments will tell!

WillowMobileSys

Curious. Did you have any issues with your app roll out?

zchichak

App roll out went smooth (thanks for checking in), but I did want to come and provide some insights.

At peak usage the application saw ~400 adds in an hour, keep in mind the application was launched via email, this means the users naturally visited the tool as they checked their emails throughout the day.
The Google Sheet rate limit was never reached, so the original question still stands. I am unsure if AppSheet runs the requests from the Creator’s account or through an AppSheet connection, with increased limit quotas. Also, I am unsure of the rate limit applies to all the User’s Sheets cumulative, or if it is 100 requests per 100 seconds per Sheet (or even worbook). This would be important to understand in the use cases that an application is invoking multiple requests, as you could then spread the load over multiple tables/sources.
From User feedback, sync times remained short, they were reported most frequently as “a few seconds”
All bots and automations remained functional, with the only hiccup coming from a table join outside of the application’s control, which impacted less than .01% of Users

My overall experience has lead my to the following understandings/assumptions, but please form your own opinions.

AppSheet’s platform can handle LARGE amounts of concurrent Users, as long as the application is read only, or if the Users are working within their own unique Keys/Rows. (avoid concurrent editing of the same data)
AppSheet is not the limiting factor, Google Sheets is. Meaning, if you have a larger rollout that may impact a large organization, take the time to develop the application with a proper database IE SQL.
100 requests within 100 seconds is a large amount to begin with - I do not see many “internal use” applications requiring anything more than this.
If you are dealing with Google Sheets, a large amount of Users, and large datasets, ensure your you do not have any formulas running on the Sheet database, rather run the calculations within AppSheet.
If you need to use Sheets due to organizational security protocols, or any other reasons, do some quick math to understand whether the application has potential of reaching the rate limit.