Slice versus Virtual Column Performance

[thethunderbird]

There are many ways to skin a cat.  One question that comes up for me fairly frequently is using Slices vs. Virtual Columns to evaluate expressions that result in true/false against an entire table when I want to re-use that expression. 

For example, if I want to run a data action for a subset of a table that matches some expression, I can put that expression directly in the "Only if this condition is true" field.  But if I want to re-use the same expression in multiple data actions and other places across an app, it can be cumbersome to maintain.  Alternatively, I can create a virtual column that evaluates the expression and just stick my [Virtual Column] in the "Only if this condition is true" field.  Or I can put the expression into a slice and limit the data action to the slice.  Both solutions clog up other parts of the management screen with unnecessary options, but is one faster?

[Swoopy]

My understanding is that when you create a virtual column, its expression is processed thru all rows of that table.

When you create a slice using the same expression, AppSheet would process it thru all rows and then pick only rows returning TRUE from the expression.

Either way the process times should be the same. My practice is to aware of using the same expression repeatedly in virtual columns and then in slices and then in actions and then in format rules, etc. This leads to chaos. Reuse the results from virtual columns across the app is good practice IMO.

From the performance point of view, try to avoid using SELECT() in actions or format rules. Either directly inside the actions/format rules themselves or indirectly via virtual columns.

But if you really need to have SELECT(), there are ways to improve app performance (It's quite a tricky process so I do not put details here).

[thethunderbird]

Either way the process times should be the same, but are they?

[Swoopy]

Also, avoid using calendar views for slice with thousands+ filtered rows.

I've experienced lag & slow (of the on-screen keyboards when using search) after open a calendar view carrying 5,000+ rows. So, I use calendar view only for slice containing 1,000 rows or less, and it fixes the lag & slow problem.

[Joseph_Seddik]

I wouldn't take into account what is easier to do (for example avoiding repeating code) and would focus only on what could translate into better performance. 

For this:

1. If filtering is done only for the sake of an Action/Template, then I would surely not put any filtering formulas outside that Action/Template.  
2. Otherwise, I'd use Slices whenever possible. 
3. And I will always try to come up with ways to avoid using virtual columns. For example, one typical vc case is calculating totals, I always replace that with actions and automation. 

[thethunderbird]

Why do you prefer Slices over Virtual Columns?

[Joseph_Seddik]

For performance considerations, anything is preferred over virtual columns. Virtual columns formulas are evaluated for every row in every table with every sync. This would lead in significant increase in sync times, especially when you have filtering formulas operating on large tables, and their impact will keep increasing with the growth of your dataset. 

[thethunderbird]

I don't think this is true.  And really, as my question was about the performance, I do want to dig into this further.

---

@Joseph_Seddik wrote:

For performance considerations, anything is preferred over virtual columns. Virtual columns formulas are evaluated for every row in every table with every sync.

Virtual column formulas are evaluated for every row in their own table with every sync.  Slice filter conditions are also evaluated for every row in their own table with every sync.

[Joseph_Seddik]

For every row in every table that has virtual columns.

[thethunderbird]

My point is, any given virtual column formula AND any given slice filter condition is equivalently calculated against every row for the table it applies to on every sync.

[Joseph_Seddik]

Compare these three cases. You want to sum a total based on a couple of conditions. 

Case 1

Using a virtual column, you have a formula like: 
SUM( SELECT(table[amount], AND([column 1] = "value1", [column2] = "value2")) )

Case 2

• Using a slice, you have a row filter condition like: 
  AND([column 1] = "value1", [column2] = "value2"))
• Plus a virtual column with a formula like:
  SUM(tableSlice[amount]) 

Case 3

• You have the same slice, as above. 
• Plus a normal column with a formula like:
  SUM(tableSlice[amount]) 
• and a Refresh Number column incremented through an action. 

Case 3 is more performant than Case 2 which in turn is considerably more performant than Case1. You have to try it yourself. 

[thethunderbird]

The example you gave boils down to eliminating SELECT() expressions and unnecessary expressions in virtual columns.  It's not related to my question.  To further restate my question, to filter down Table for rows where Column is not blank (for example), which is faster to sync?

Case 1:

New [Virtual Column] in Table with formula expression ISNOTBLANK([Column])

Case 2:

New Slice of Table with filter expression ISNOTBLANK([Column])

That's it.  That's the whole question.  There are many reasons to do this kind of thing which we don't need to get into.

[Joseph_Seddik]

---

eliminating SELECT() expressions and unnecessary expressions in virtual columns. 

That's the obvious, axiomatic and practical reason for using slices, which additionally I highlighted in red. Otherwise, why would one use ISNOTBLANK() and the likes in a virtual column? This makes no practical sense! 

You might've been asking a theoretical question for which I was answering from a practical POV, and this could've lead to misunderstanding.

Functions that are cheap like a simple ISNOTBLANK() wouldn't result in a noticeable performance difference whatever way you'd be using them. So, figuratively speaking; since there's no practical reason to do so, there would be no performance difference between a slice and a virtual column both using something like ISNOTBLANK().

[thethunderbird]

Yes, it's a theoretical question. 

The evaluation time of ISNOTBLANK() (or whatever other expression one puts in the VC/slice filter) is not a consideration, as it will be evaluated on the same number of rows i.e. all the rows in the table it applies to.   We're on the same page there.  The expression could be anything, even 1+1 or TRUE.  For the sake of the question, it does not matter. 

What's important is that there is an expression, it would be the same in the Slice or the VC, and it's not appropriate for a formula in a static column.  We can talk about those reasons another time.  But here's one example: you have a table with a timestamp column and you want to do a bunch of different things in different places (add UI colors, run actions, trigger bots, etc.) with all the rows with timestamps 24-48 hours old.

There are other factors beyond the evaluation time of expression when considering performance.  Adding a new VC or Slice adds a bunch of UI stuff, for example.  Those differences are what I'm asking about. 

[Swoopy]

---

@thethunderbird wrote:

Adding a new VC or Slice adds a bunch of UI stuff, for example.

---

Agree, adding a slice (editable), AppSheet adds detail & form views automatically. Not only the expression counts, let alone you need to retouch the additional views (if you care about user's experience).

[MultiTech]

💯 all around.  

Except... why use "actions and automation" to update an order total... when a simple app formula inside a physical column would suffice?  Why all the complications of actions, bots, figuring out the triggering event, coming up and the chain of updates that need to happen.... etc. etc. 

Why not just set the app formula of [Order_Total] = SUM([Related Order_Details][OrderDetail_Item_Total])

---------------------------------------------------------------------------------------------------------

I've noticed a shift in execution in the past 6 months.  Instead of utilizing a formula, people have instead starting making giant complicated automations to update things.  When if you look at what's going on, all you really need to do is what I've outlined above (in most instances).

 - The only thing left to do with what I've outline above, is to run an update on the record (using the quick update system) - and you're done.

Just an observation I've made over the past few months. 😉  It's great that people are making use of automation... I would just advise not going overboard with it.

[Joseph_Seddik]

I agree with you, couldn't I? 😄 Here is the thing:

1. You'd still need an automation to trigger formulas recalculation in a physical column 🙂 and this is what I do in most cases. 
2. There are cases a bit more complex than calculating a total for an order. One particular case I have is this:
   
   1. The customer has a dozen sites, each with its own registers for cash and card payment.
   2. There are also central registers that record cash, credit cards and also bank transfers,  PayPal, and other methods. 
   3. All registers can have inbound and also outbound transactions. 
   4. All transactions are recorded in a centralized Transctions table that references the different registers. Several hundreds of records are created on a daily basis. 

In this latter case, in the beginning I had SUM([Related Transactions][amount]) in the Registers table updated through incrementing a Refresh column. Later I replaced this with an action that just increments or decrements the physical Total column with the amount of each transaction once it is created, because this is noticeably visually faster. So instead of recalculating the long history of transactions belonging to each register, every time a transaction is created, I just update its current Total with the transaction amount.

I also maintained a Recalculate Total action button, just in case, with SUM([Related Transactions][amount]), within each register, which as would be expected, is noticeably slower.

This is the only case I'm having this mechanism, but I remain open to improvement suggestions 🙂

[Steve]

Virtual Columns

Virtual columns only update under specific conditions:

1. During a sync.
2. When its row is added or updated.
3. When a child of its row is added, updated, or deleted.

If the value needs to reflect conditions that change outside of those times, a virtual column is not a good choice.

At sync, the App formula expression for each virtual column is evaluated for each row of the table that includes the virtual column, whether the result is expected to change or not. An inefficient expression--or even an efficient one that looks at a lot of data--can hurt performance, and the drag will get worse as the data set grows. For this reason, virtual columns that use queries should be avoided in tables that are expected to be large. When queries are used in virtual columns, they should avoid referencing large data sets.

Slices

A slice updates any time any row of the underlying table is added, updated, or deleted, or when any data value its row filter expression uses changes.

Slice updates are performed by the app itself and do not require a sync. Consequently, slices are always current, but their complexity can affect device performance.

Only if this condition is true

The Only if this condition is true expression for an action is evaluated each time any data value the expression uses changes.

Like slices, Only if this condition is true expressions are evaluated by the app itself.

[SkrOYC]

I'd just add that I prefer Slices if the app is going to be used on desktop and good mobile phones since calcs are made on the device the app is running afaik. If you are fighting against the system-generated detail and form view it's because you have a slice that's not read only (meant for the purpose of just calcs).
And if you don't need to mantain a lot of different views for the same table, just make an action on row select that links to that row on the original detail view of that table.

About the usage of VC, I noticed lately that I can replace most of VC with real ones and make sure to update them by using a simple number column (mentioned by @Joseph_Seddik  and @MultiTech's awesome post) that's updated via action attached to whatever form I need to attached it to thanks to the "Execute an action on a set of rows". So I can update that row based on any row add or edit.

In other words, the avoidance of VC is good in almost any case, even the ones that may seem like they are not