Informal poll regarding app "size"

Dialect_Junk · 02-07-2020 08:26 AM

I’ve built several apps for my work with the idea of preventing, as Santiago puts it, building a “Franken-App.” But now there’s a push at work to consolidate some of these apps into one. This consolidated app would have 16-18 tables, upwards of 200 columns, dozens of workflow rules and actions. My concern is the impact this would have on sync times and usability in the field. So, Appsheet Community:

What’s the “largest” app you’ve built (tables, columns, etc.)?
What’s the most resource intensive app you’ve built (calculations, workflows, actions, etc.)?
Do you have a “benchmark” where you start getting concerned about app size and discourage adding tables, columns, workflows, etc.?

Thanks and I can’t wait to hear what you folks are building out there!

Jonathon

I have two applications which some may consider ‘franken-apps’… I haven’t experienced any concerns regarding load times or user experience yet.

One application is for structured inspections; it’s spec view is kindof cute:

Another app I have includes much less structured inspections and could be considered multi-purpose:

Still, I would only seriously consider franken apps where you are backending the data to a sql server or where the data volume are and will remain relatively low.

Dialect_Junk

This is great info. Gives me slightly more peace of mind… Thanks for sharing!

MultiTech

What some call a “Franken-App” others might call a Platform. #Branding lol

MultiTech

Appsheet apps can get very large, and as long as you go about building your app in an efficient way then you can create some incredibly complex systems.

One client of mine is a tele-health company, dozens of people enter patient data and doctors remotely review this data. Here’s a picture of tables for that app:

The actions page is nutz:

https://p56.f1.n0.cdn.getcloudapp.com/items/YEuAqvrN/Screen+Recording+2020-02-10+at+08.24.25.29+AM.g...

Once you get over 20k records in a google sheet, you really need to think about how things are split up, stored, etc. - SQL is the way to go if you’re going to have a ton of records.

Jonathon

For apps with large datasets, one common method for reducing load times is to employ security filters to reduce the amount of data loaded. The performance benefits here are two-fold:

The physical datasize loaded over the network is reduced
The in-app calculations are reduced as less data is present

One problem with Google Sheets, is that SQL queries against it are inherently less efficient than SQL queries against a true database. When you have a security filter on a Google Sheet datasource, say for example. USEREMAIL()=[email], appsheet still loads the entire google sheet and then performs the filter. The same query against a SQL database would be handled by the database - only the requisite data would be transmitted over network.

For most instances this isn’t a problem; but if you want to have more complex security filters, you will see much better performance on a true SQL database.

This is my current understanding - I would appreciate if someone from AS could verify this though.
@tony

Edit: another question I have regarding relational databases vs google sheets is, how are data types handled?

For example, in a SQL server I can make use of tinyint, bit, and char() datatypes whereas a Google Sheet saves everything as raw text. Is it reasonable to assume that a true/false record stored in a google sheet would be physically larger for AS to read than a true/false record stored as bit() on a SQL server?

MultiTech

Security filters apply BEFORE the data is sent to the app, so the app only ever has the dataset specified by the criteria of the security filter.

But yes, security filters on a table connected to a SQL are much faster. On one app of mine, we had things hosted in Gsheets and had load times of around 30 seconds; after migrating to SQL we’re down to 10.

I would like to know this as well.

Steve

Correct. That’s the “security” part.

tony1

Security filters are mostly for:

Security - preventing certain data from reaching your user’s device that they shouldn’t have access to
Performance - reducing the amount of data sent to your user’s device from appsheet.com

For certain data providers, like SQL databases, some security filters can be “pushed through” to the data provider to get a boost in performance. Compare these two cases:

You have a security filter on your table backed by Google Sheets. AppSheet reads the entire sheet from your drive, then filters it.
You have a security filter on your table backed by SQLServer. AppSheet converts your security filter to a query, which is run on your database, returning the filtered data.

In the first case, the entire dataset has to be transferred from your provider to appsheet.com, which can be slow if your dataset is big.

In the second case, only a fraction of the dataset is transferred from the provider to appsheet.com. Further, the query might execute quickly because it might take advantage of database indexes.

AppSheet only “knows” how to convert certain types of security filters into SQL queries, though. For example, a simple [SomeColumn] = "Some Value" will be translated into SELECT ... WHERE SomeColumn = "Some Value".

Koichi_Tsuji

One thing it come up with me which is related to sql and performance issues. Most of my production app is running on sql server , but there is one thing I have been waiting for appsheet team to solve .

When we do have massively large table and user not to update same table, then it is making sense that app creator set the table read only which makes app to read rows faster. But it is true when we set the data to spreadsheet. When we put table to sql , then appsheet is keep reading raw data all the time which require significant amount of time. Just to share my experience.

Appsheet Dev team should have been working on this but I’m not sure where we are either close to get a fix or not.

MultiTech

I’m looking to get every ounce of performance I can here Tony.

If I were to take my security filter and converted into a query, then create a view in my SQL server, then use that view as the data source in my app… Would I get a performance boost?

My thinking is this:

if you’re having to convert the formula, then run the query, in order to get the data… if I have a SQL view with the query already there, then the data should already be sitting there yes?
I’m sure there’s a requery of the data, but at least we would be getting rid of the step of the security formula-query conversion step. (But does this even really matter? Is the speed benefit that I would gain here even worth the effort of creating all of that SQL server stuff?)

Thanks!

Koichi_Tsuji

Hi Matt,

Not sure if this is information you are looking for though, but do you make INDEX on your data tables you placed on your sql server?
I do believe sql index may help to improve the performance to read the rows especially when we do have sort of SELECT xxxx where xxxxx type of statement to query to your data table in the sql.

I cant make quantify how much the speed of the query improved, but by placing index, my app responded far better.

tony1

@MultiTech_Visions Yes, if you create a database view and use that to back your AppSheet table, you may be able to get a performance boost. However, you might lose the ability to do adds/updates/deletes on your table, depending on your database and the query you use to create your view.

Dialect_Junk

…wow. And here I was worried about 16 tables and a dozen actions

Thank you.

AndreSpeek

Woops, that must have taken you an afternoon or two…

So did you do this on a Google Sheet or on a Database? If on a Google Sheet, did you consider doing this on a Database?

MultiTech

This solution was originally built by the client - built inside a single spreadsheet with some monster formulas - eventually we expanded into many spreadsheets; and now it’s a mix of some very large tables hosted on a SQL with some others hosted through Gsheets.

There’s a trade of for each, but my opinion is the following:

SQL tables are good for large tables, meaning they’ll have upwards of 50k records;
Gsheets are good for smaller tables, especially if you wish to “play” with the data (creating other integrations, charts, reporting, etc.) or if you wish others to be able to use the data somehow (it’s easier to share a Gsheet than access to a SQL server).

Ron_Mansolo

For one home health company I build separate apps for different departments. The only problem are with department heads and executive types that want to see everything. In those cases the biggest would be a dozen tables and not more than 30 columns.
Would definitely be concerned about speed of the app and user friendliness especially with new hires for an over-complex app.