considering SQL database hosted in cloud...wh...

(Sansao Negao) #1

considering SQL database hosted in cloud…what is the better:

Have

a lot of fields: ENUM populated with VARCHAR and save varchar in database (sync time in better because the Appsheet server fetch less tables in data source, but database size is bigger (the same VARCHAR is stored thousands times and maybe processing time is less…and cloud storage is more cheaper than processing, but with enums the maintenance can be complicated to delete, update items etc)

Or use a lot of ENUM TYPE REF (filled with valid if of a TABLE) and save int in database and maintenance the database short but maybe increase processing time?

In the cloud we pay for CPUs, storage and bandwidth…help!

1 Like
(Reza Raoofi) #2

How many data rows and tables are you talking about that you are concerned about CPU processing time and Storage?

When it come to SQL databases I think the best approach is to go with design and business/real life model of entities and their relationships; make them a separate table with Ref column referring to them if that is really an entity in the business model where it can have its own relationship with other entities and perhaps having its own attributes (columns) down the road.

Then if you want to cut on bandwith/CPU/Sync times, find a criteria to filter down the downloaded rows to AppSheet using proper Security Filters, instead of sacrificing your normalized relational database design.

1 Like
(Sansao Negao) #3

Thank for your response @RezaRaoofi, I have 20 tables can be modeled like database SQL table or like enum in Appsheet framework and 30 other dynamic tables…for that 20 tables they have 2-20 values for choice…

For dynamic tables, my main table will insert 1000 rows per year for user, with 100 user’s I will have 100.000 rows per year

So that 20 tables have relationship with only 1 or two tables ( sex, color, types, constants, and other can be change with time it’s hard to known now)…

But I perceive delay with tables, maybe 5 seconds when I changed 20 tables from enum text to enum ref table.

(Reza Raoofi) #4

If you are planning on having 30 to 50 tables in your app, you might need to consider breaking it into more than one app rather than trying to save text instead of Ref in those tables.

(Sansao Negao) #5

@RezaRaoofi I agree with break the app in future, but when you have enums in a app you choice a database table over Appsheet text enum, right? Only for confirme us conversation?!

(Reza Raoofi) #6

In the second paragraph of my first comment, I was trying to imply that you need to first think about the main entities in your model in the business requirements; that has the higher priority and will identify the rest, rather than messing with your database model for things like sync time, and CPU usage.

Not all lists are actually an entity in your system, if they are, then you should consider a table for them. Then use Ref column for relationship between them and other tables, and of course the Key ID will be stored in the Ref column, as opposed to other text columns like name, description, etc.

Sometimes those lists are only really some status values or fixed descriptions; for example City and Country names in some tables could be easily considered Enum, but in another system the same country and city could be really an entity that has a whole bunch of attributes of their own and relationship with other entities, then in that case they have to be considered as tables, with separate column for each attribute, and you would need to analyze its relationship with other tables too.

(Reza Raoofi) #7

I wouldn’t worry much about AppSheet and saving 0.5 seconds here and there, because you are now multiplying that by 30 tables x 100,000 rows, etc.

Down the road when/if your app slows down you can save much more by using security filters and breaking one app with too many tables into smaller apps, than by changing tables into Enum.

1 Like
(Sansao Negao) #8

Thanks @RezaRaoofi, you are awesome…what does me to think more about this is the cloud environment, where we can see in a lot of moments people using noSql or Denormalized tables filling the database with a lot of VARCHAR because the STORAGE is more cheaper than PROCESSING in CLOUD ( I will use Amazon RDS and Amazon S3),

in our case: we will use a lot of Lookups to renderize the app in my device and a lot of lookup to generate each report(pdf) of the user…how you can see I am trying to analyze the complete environment of the Appsheet + cloud and understand the best practices in this way…like this example for use table or VARCHAR…in a report (template pdf) or this will be

[status]

or will be

[lookup(fk_status, status, id_status, ds_status)]

And this is for ±20 tables and for each report of each user… understand the complexity of decision?

And if I save ints over VARCHAR maybe the traffic egress would be more little or not if Appsheet server query the same table for each sync for each user, because the data source is cached in Appsheet server only for 5 minutes, then use a table instead a VARCHAR enum can let us to query the same tables hundreds/thousands times in a day or week…spending bandwidth and others metrics like memory and cpu.

Have anyone of the Appsheet can explain if the table is queried all the time and we must to use enum type VARCHAR to dont do it?! You can mark anyone of the team here @RezaRaoofi???

(Reza Raoofi) #9

Yes, AppSheet does sync all tables, if they are on a cloud SQL database; those rules you read in documentation about checking to see if a table has been changed works only for cloud spreadsheets like Google Sheet, otherwise AppSheet cannot recognize when a database table inside an Amazon RDS instance has been changed or not, so it will requery all SQL tables upon each sync.

So I understand your concern about egress due to several syncs per user per device. The only controlling tool you have in your hand is security filters. You can use that along with User Settings page; once each user enters data in that page and saves, AppSheet initiates a sync that would query all tables and this way you could feed the Security Filter criteria from values entered in User Settings.

Regarding denormalization, I agree that cheap storage is encouraging it, specially for free data sources like Google Sheet everybody can save the whole text instead of a small key ID, so these days I often normalize not to save storage, but for flexibility in future updates, for example if a lookup table’s description column changes, I wouldn’t need to update all those VARCHARS on the many side of a one-to-many relationship.

Regarding the lookup() formula you mentioned, I always try to use De-reference on Ref columns:

https://help.appsheet.com/expressions/expression-types/dereference-expressions

(Sansao Negao) #10

I definitely will improve my user settings to filter a lot of lookup tables using security filters after talk to you, thanks

Maybe I can start with normalization and denormalize over demand, when I need this…

Ref columns is not a option for lookup tables (read only almost ever), I only use it for tables that can be add on app,not for tables read only because it generate virtual columns (mandatory) and increase the sync time, see this:

plus.google.com - WOW, just shaved a whole second off of my sync times by changing a lot of my …

WOW, just shaved a whole second off of my sync times by changing a lot of my … plus.google.com

(Sansao Negao) #11

Because of this I started to use enum type ref for read only tables (my enums) and started to talk with you about VARCHAR x int

(Reza Raoofi) #12

Yea, that link was related to a hack workaround that is undocumented and mostly promoted by the same person, I personally do not like those unorthodox methods!

I agree with the part you said: …Maybe I can start with normalization and denormalize over demand, when I need this…

If those lookup tables are read-only, then you could denormalize them and just use simple Enum and save text value in the table (instead of int); this way in reports you won’t need join in SQL or using De-reference in AppSheet.

(Sansao Negao) #13

Good comments @RezaRaoofi, but I think that Appsheet don’t do joins in SQL ( when I use Lookups they convert to select(any( and execute this in memory of Appsheet server because they don’t generate foreign keys in SQL databases when

we migrate from sheets to SQL, I include fks for security.

(Sansao Negao) #14

Delay when sync ( Appsheet framework don’t manage very well the performance for tables, they are catch in data source for each sync, same if the data source don’t change) because of this I am asking for myself if i must change the tables for enum base Type Text.

(Sansao Negao) #15

@Levent_KULACOGLU @Aleksi_Alkio @MultiTech_Visions +Praveen Seshadri

(Sansao Negao) #16

+Praveen Seshadri

(Multi Tech Visions) #17

@Sansao_Negao @RezaRaoofi Good discussion.

I’ll add a little from my experience with large apps.

I’ve got a client that’s a tele-health company, and they’ve got an app that’s full of complexity and automation - and bloated with data.

My original design was to use de-refs to facilitate easy data pull, but it had an unexpected side-effect: creating a reference from one table to another creates a REF_ROWS() that cannot be deleted.

(The best you can do is change the formula you “List(”").

But after removing the references, and using lookups instead, I was able to reduce the sync time.

This is due to the fact that when you sync your app, all the REF_ROWS() are calculated - and if you’ve got 50k records thats a lot of sorting.

And if the references are just there for easy data pulling, NOT an actual entity-entity relational reference, these extra VCs are complete unnecessary and only add to the sync time.

LOOKUP() I’ve found processes extremely fast.

While a de-ref is really (I mean REALLY) helpful with simplifying your workload as an AppSheet dev, when you get a data bloated app it can be more of a hindrance than a help.

Unless the connection between two tables is an actual relational connection, I wouldn’t use a reference .

If your app is going to get large and contain a bunch of references, you could be looking at a longer loading time is all I’m saying.

2 Likes
(Sansao Negao) #18

Very useful @MultiTech_Visions I agree with you about the virtual columns… One last question…for typic enums like sex(male,female), color (blue, …), Etc

And for enumlists like theme (technology, human resources, etc)

You prefer to save in a cloud database SQL INTS (using tables for color, sex) or VARCHAR?!

Considering the normalization, the redundancy, the cloud architecture and pricing, and the Appsheet architecture of syncing.

(Reza Raoofi) #19

@Sansao_Negao I have not had a chance to read through all new comments on this thread yet, but when I said de-normalize and then you would not need join in SQL, I did not mean appsheet would use SQL join; I meant generally speaking your job would be easier in the future, because you will not need to write join queries on the SQL side for reports, neither you would need De-referencing on the AppSheet side. So de-normalize where you can and when those look up tables are mostly read-only.

(Multi Tech Visions) #20

@RezaRaoofi I’ll admit… I had to google “de-normalize” (^_^)

I have no formal training in database design, 100% #CitizenDeveloper here, and had a vague idea of what that term meant - but not the specifics.

hmm… something to look into. :wink: