App dragging, Too many Tables (56)? file size is only 2.8mb...

[ZOCO_Unlimited]

Well, I'm in a conundrum. For years I have run multiple small apps and as I have the need to update, I've had to do the cumbersome task of updating each one. I finally decided to build a single app for everything in hopes of a central management platform for our business. After months of building, everything seemed grand.... Until I copied in all of our existing data for the last year into the new app. Granted the entire file size in google sheets is only showing 2.8mb, it is SLOW. Slow to load, slow to sync (avg 13 seconds) , click on something, wait for the detail view to populate, slow.  Other issues it will totally Glitch out, jumpy spaz screen, won't load and freezes.. 

Of course, now I start to look at why and come across the Limits section in Appsheet Help Limits on tables per application  . I did make all of the tables in one file so that helps but there are still 59 tables. Our last apps averaged about 26 tables and ran great. 

I have run the performance analyzer and have found the tables w/ the longest time's VC's seem to be the culprit a vast majority of the time.  I started taking ref columns and changing them to enum base type ref to remove the bloat and then using LOOKUP() for the previously dereferenced columns that I need but this feels backwards to me. I tried deleting the Related columns that come w/ referenced tables but they don't stay deleted..  This is where I have stopped, I know I need guidance or help to move forward... 

Now Before I start tearing this thing apart, I'm reaching out to see if anyone out there can help me identify If I can make this work without removing what was built? 

Google Sheet Size 2.8 MB

Excel Size 7.96MB when downloaded

55	Table Count	55
Use Case	Type	Table	Able to Remove?	Server Caching?
All	Object	Customers
Field & Office		Customer Contacts
Office		Customer Cost Centers and AFEs	Can Vlookup sheet formula	Read Only
Office		Cost Codes	Can Vlookup sheet formula	Read Only
Field & Office	Object	Custom.Locations
Field & Office	Object	Locations
Office		WOGCC Codes and symbols	Can Vlookup sheet formula	Read Only
Field & Office	Object	Dispatch
Field & Office		Dispatch Activity History
Field & Office		Dispatch Job Tasks
All	Object	Equipment
Shop		Equipment Parts
Shop		Equipment Service
Shop		Equipment Service Notes
Shop		Equipment Service Tasks
Office		Insurance Trail
All		Permits
Office		Checklist New Asset
Office		Checklist Removed Asset
All	Object	Fuel Log
All	Object	Rentals
All		Rented Equipment
Office		Rental Billing
All		Rental Equipment History
All	Object	Messages
All		Message Activity
All		Likes
All	Object	Personnel
Field & Office		Time Clock Import
Office		Checklist Employee File Review
Office		Checklist New Employee
Office		Checklist Released Employee
Office		Access Control
Office		Safety and Training
Office		Employee Onboarding
Field & Office	Object	Rigs
Field & Office		Rig History
Field & Office		Workover Rig Schedules
All	Object	Vendor
Field & Office	Object	Work Orders
Field & Office		Work Order Activity
Field & Office		Work Order Report
Field & Office		Work Order Split
Field & Office		Job Type	Can make enum list
Field & Office		Related Tickets
Field & Office		Pictures
Field & Office		Attachments
Field & Office		Production
Office	Object	Invoice
Office	Object	Items
Office		QB Item List
All		App List
Office		Reoccuring Tasks
Office		Checklist List
Office		Schedule

[Marc_Dillon]

56 tables is A LOT for a single app. I'd recommend only having a half-dozen or so editable tables per app, and adding in a dozen more smaller or "reference" tables is fine as well. You basically went the opposite way of what the platform was designed for, multiple smaller apps is better.

The virtual column performance analyzer over-represents the impact of [Related...] REF_ROWS VCs. Basically ignore all of them from that chart. They don't actually impact performance hardly at all. The only reason they appear on the chart like they do is because they are calculated in parallel with the loading of the related table's rows.

An example to help you understand: child_table has a Ref column to parent_table. child_table has several thousand records, and takes 5 seconds to load all of those records. So, the [Related child_tables] VC on parent_table will also show as taking 5 seconds to compute. But those 5 seconds are the same whether or not the Ref or REF_ROWS column is there.

To improve VC performance, focus on all of the other VCs that do actual calculations. You should limit those to an extreme amount on an app as large as that.

The other best way to improve performance is to implement strict Security Filters. Basically, just limit the amount of total records the app has to load at one time. Also read up on "horizontal scaling" if you're finding it tough to specify non-variable Security Filter rules.

Security Filters are also greatly more effective on SQL databases than they are on GSheet tables. So consider that upgrade as well.

[Markus_Malessa]

While I can't disagree with what you said, I seemingly also can't ignore that Appsheet seems to take 'longer' on everything since about a month ago or so. Including syncs and I have especially noticed it on processes now take 2-3 times longer than what they used to. Recently I have found myself entering data into Google Sheets and then processing via a script to get the data into the MySQL database because it goes so much faster. Even under the most extreme case where 1 row of data results into 52 new entries across 4 tables it only takes 5-10 seconds using a script.

[WillowMobileSys]

First, it must be stressed that poor performance is mostly tied to amount of data to be processed and not really the size of the app or number of virtual columns.

The very first thing to look at is reducing row size.  Perform an internal assessment of what data is really needed in each table for each user for the day to day operations.  In this assessment, watch out for calculations that depend on ALL rows being present - I'll call these history-dependant calculations.  If you have this situation in your app then the very first step is converting these history-dependant calculated values into Table-stored values that are adjusted with each new row added/edited and/or deleted.

Once you have eliminated any history-dependant calcs AND determined how much history is really needed,  then your first line of defense is to apply Security Filters.  This alone can cause a HUGE difference in performance.  Not only does it reduce load times of data but any Virtual Columns will also operate over fewer rows reducing their processing time as well.

There are many other steps  that can be taken to help improve app performance.  The above is the most critical and can take some time so I'll pause here to allow you to absorb the suggestions and assess your own app.  Please ask any questions or if you believe you are ok with the above, post so and we can move on to other options.