Virtual Columns Vs Database Table Column

Can some forum members and appsheet staff please identify and clarify the performance aspects of Virtual Columns Vs Database Tables Columns?

I have looked at the guide:

I vaguely remember an Appsheet Webinar last year which discussed this at the end.

With that, I am currently developing Appsheet Apps with where:

Virtual Columns are

  • Updating their value every time the database row is read.
    Advantage:
  • Values are dynamic and are always up-to-date
    Disadvantage:
  • Slows down the application with the scale of the number of virtual columns and database rows.

Database Table Columns

  • Updated their vale only when the database row is updated (or initially created).
    Advantage:
  • Avoids slow down of the application by not having to do any extra computations for dynamic values which seldom change during any user session.
    Disadvantages:
  • Values are static and can only be updated manually, e.g. data entry or action/workflow combination.
  • Takes more database storage

Is anything wrong with the above?

Are there any other factors to consider?

Thank you for your comments!

It depends what do you want to do with those calculations. Are you looking for a solution where you need to update another record in another table when the data is changed or is this happening inside of that same record?

It could be either situation.

In generally there is no correct answer in here. It totally depends on your table/column structure. One solution could be the Event Action if your rows need to be updated seldom. Then you could create an Event action that would update another table/record (or records). When you sync the app, no virtual columns are needed.

I am also interested in the performance tradeoffs.

The quick answer is only use virtual columns if you have to.

Performance tips
= Use security filters to limit rows if you have a lot of virtual columns
= Start of virtual columns with something like IF([Status]=“Dead”,"",… so it quickly defaults to blank rather than doing a big calculation for a row your not even bothered about
= Note a virtual column can sometimes appear to give different data when looked at in the form vs inline view

2 Likes

Thank you SImon. Very Logical.

1 Like

@aucforum The responses above give a good layout of the tradeoffs between the two. I’d just add that virtual columns are simpler in a way: you don’t have to think about whether they contain the latest information. The cost is in performance, but the platform tries to optimize that as much as possible.

One situation where you’ll need a physical database column: if you need to access a computed value outside of AppSheet. For example, if you have another, non-AppSheet app that depends on the data.

2 Likes

I would note this post by the grand master:

1 Like