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
Database Table Columns
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
Thank you SImon. Very Logical.
@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.
I would note this post by the grand master:
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.
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
User | Count |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |