Virtual Columns Vs Database Table Column

aucforum
Participant V

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!

1 11 3,380
11 REPLIES 11

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

Thank you SImon. Very Logical.

tony1
Participant V

@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.

aucforum
Participant V

I would note this post by the grand master:

dkoosis
Participant I

Is there a performance difference between virtual vs database for Show columns?
My gut would be that virtual might perform ever-so-slightly better, because the Appsheet server would never need to read the empty column from, say, Google Drive.

My personal opinion is that there is no value in using a real column as a Show-type column.

Nicolรกs
Participant IV

what about this case

I have a parent "Evaluations" table with columns  title, date, subject, semester, and also i have a child table "Grades" with studentID, evaluationID and grade.

My problem is that in some views of the grade table I want to group the data using data from the "Evaluations" table, like date, semester, subject, etc. So my current solution was to use autocompute to copy the parent columns into the child, so in the last one i have  

studentID, evaluationID, grade AND date, subject, semester, title

But i feel this is not the ideal aproach since there is all this repetition of data in my database and i supoused that the idea of relational databases was to take advantage of the Key references. Any way, what would be better in this case, ยฟcopying to the child columns or have virtual column there? Consider there will be aproximately 20.000 rows from Grade Table

Top Labels in this Space