Delay on SUM-SELECT function when working in same table

[Hyman_van_Zyl]

Hey Guys,
I have a Transactions table with an Amount column. If I add a SUM(SELECT virtual column to this table to total all amounts, with or without conditions, it takes about 10 to 30 seconds for a new transactions’s amount to be added to the total.
Is this normal? Is there a way to formulate the SUM expression as to avoid the delay?
I mean, when I have a SUM(SELECT virtual column for Amounts of the Transaction’s table in another table, like an Account table for instance, then any changes reflects immediately. So the delay seems to only happen when I work in the table on which the sum select is implemented.

[WillowMobileSys]

If you have a Virtual Column with SUM(SELECT…) on the same table as rows you are inserting/updating, then that means your recalculating the Amount column for EVERY row in the table each time you add or update the value that is summed. You are experiencing the slowness due to read and writes being performed on every row. As your table grows, so will the performance time.

Bottom line is that you have a design flaw. If it is important for the summed total to show on every row, then a better solution is to place that Amount column into its OWN table, even if it is just that single column, use a Workflow rule to run the calculation when one of the values included in the sum is added or updated (this can be a little tricky if you haven’t done it before), then in your Virtual Column Amount - use a LOOKUP() function to retrieve the calculated sum. This way the calculation runs only ONCE for each add/update.

I hope this is helpful!

[Hyman_van_Zyl]

Hey John,
Thanks for the support.
I think I got it sorted now. I followed your advice to process that data from another table. That seems to solve it.
Thanks
Hyman