Slow synchronization of virtual column calculation result

[muhammadjavohir]

Hello.

I've got a part of the app where I should calculate the remaining raw material balance/amount. The balance is changed based on raw material purchase, usage and transfers.

Currently I'm using this expression in my "Balance" virtual column.

IF(
[RawMaterialWarehouseID].[Type] = "Bosh ombor",
[InitialBalance] + SUM(
SELECT(
RawMaterialPurchaseDetails[Amount],
AND(
[RawMaterialPurchaseID].[IsDelivered] = TRUE,
[RawMaterialID] = [_THISROW].[RawMaterialID],
[RawMaterialWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
) + SUM(
SELECT(
RawMaterialTransfers[Amount],
AND(
[RawMaterialID] = [_THISROW].[RawMaterialID],
[ReceivingWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
) - SUM(
SELECT(
RawMaterialTransfers[Amount],
AND(
[RawMaterialID] = [_THISROW].[RawMaterialID],
[SenderWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
),
[InitialBalance] + SUM(
SELECT(
RawMaterialTransfers[Amount],
AND(
[RawMaterialID] = [_THISROW].[RawMaterialID],
[ReceivingWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
) - SUM(
SELECT(
RawMaterialTransfers[Amount],
AND(
[RawMaterialID] = [_THISROW].[RawMaterialID],
[SenderWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
) - SUM(
SELECT(
RawMaterialUsages[Amount],
AND(
[RawMaterialID] = [_THISROW].[RawMaterialID],
[RawMaterialWarehouseID] = [_THISROW].[RawMaterialWarehouseID]
)
)
)
)

The expression is working. But the results are being synchronized in about 1-5 minutes and after manual refresh/synchronization triggering.

Could you recommend methods to make this expression/synchronization work faster/instantly?

[MultiTech]

So... many... selects!!!

There's your problem!

• Select() is a brute force method for deriving your answer, and if you've got a large dataset - it's a recipe for disaster if you use it a lot.

You'll need to restructure your database to account for more relationship connections, add supporting tables, data subset slices, to help provide the connective tissues between your tables.

I fed your question to Appster, here's what it had to say about things:

_____________________________________________________________
From Appster

Hello, and thank you for reaching out with your query on improving the efficiency of the balance calculation in your AppSheet application.

From your description, it appears the calculation for the raw material balance relies heavily on multiple SELECT() statements. This is indeed a heavy computational task which would explain the delay you're experiencing, especially if you have a large dataset.

To optimize the performance of your app and reduce sync times, consider restructuring your data schema to avoid using these SELECT() statements and instead leverage more efficient constructs like referenced rows and virtual columns within your related tables. Here's a strategic approach to restructure your app for better efficiency:

• Initial Balances: Ensure that each raw material and warehouse combination's initial balance is stored in a way that can be efficiently referenced, possibly within the RawMaterialWarehouse table itself if not already there.
• Purchases: Rather than summing up purchases each time, maintain a running total in the RawMaterialWarehouse (or a dedicated junction) table. Whenever a purchase is marked as delivered:
  • Use an AppSheet Automation Bot to increment the 'CurrentBalance' column in the RawMaterialWarehouse table with the amount purchased. Group actions may be necessary to handle the logic previously covered by the SELECT() statement but in a more efficient manner.
    
    
• Transfers: Similar to purchases, maintain running totals that auto-update using bots when a new transfer record is added. You would have an action to increment the balance in the receiving warehouse and another to decrement it in the sending warehouse.
• Usage: Adjust the running total to decrease from the balance on the RawMaterialWarehouse table each time there's usage logged.
• Raw Material Balance: Instead of recalculating the balance each time, have a [CurrentBalance] virtual column in RawMaterialWarehouse that reflects the balance without requiring a recalculation. It will be equal to [InitialBalance] plus any increments or decrements done by the actions.

By moving the balance-calculation logic to actions that trigger on data changes, you reduce the need for AppSheet to continually re-evaluate heavy SELECT() expressions during sync, which should considerably speed up the process.

Remember to also use Slices and Virtual Columns smartly by ensuring any formulae they contain do not trigger AppSheet to perform full-table scans unless absolutely necessary. Use reference rows and "data subset" Slices to limit the scope wherever possible.

Please keep in mind that an exact detailed solution would require a more comprehensive understanding of your data model and existing app structure. Nevertheless, this should provide a good starting point for rethinking your approach to balance calculations.