First, AppSheet proprietary Databases are FREE but in their infancy, have some issues and are not ready for large apps due to limits. They are well suited for only small-ish apps at the moment, in my opinion. So let’s set them aside.
For small, and even medium sized (tens of thousands of rows - all tables) apps, without a doubt I strongly encourage Google sheets to be used.
So, Let’s focus on SQL Databases versus Google Sheets with regards to large apps.
When apps become large, e.g. hundreds of thousands of rows OR tens of thousands in several tables, performance starts to become a concern. All apps, regardless of datasource, will degrade in performance as they grow in data size. I do believe with certain care taken with the structure of tables, usage of Virtual Columns, and archival of old data, Google sheets can serve even most large apps very well for an indefinite period of time.
However, there is effort=cost in maintaining those Google sheets. Virtual Columns need to be scrutinized carefully. Archival processes need implemented and managed to reduce the size at the source (for faster loading). Processes need to be in place when older rows need to used or reviewed. Backup processes need to be custom built.
When using an SQL database, data reads are already optimized for fast retrieval. But AppSheet can apply Security Filters to data read process from a database to limit the rows loaded - making reads, and subsequent server-side processing, even faster. No archival is necessarily needed though archiving- even in a database, will make the data reads that much faster still. While AppSheet apps using a database can read data faster, the same effort is needed to be sure Table structures are suited for performance and VC’s are used only when needed and appropriate, otherwise they kill performance. Most databases have their own backup features.
The Differences??
It boils down to cost versus effort.
SQL Databases will cost extra but can “naturally” speed up read times. AppSheet Security Filters can serve as a kind of archival eliminating the need for manually built archival processes. Most db’s have backup facilities eliminating the need to build those.
Google Sheets take extra care and effort to ensure best possible performance with the need for manually built processes to archive data to achieve faster read times and ensure data security with backups. These processes will need to be maintained and updated through the life of the app.
I hope this helps!