We are working on optimizing our app to work as fast as possible. One thing we are looking at is calculations that we have in table columns. Can anyone shed some light how much impact it has to embed a calculation (especially a complex one) in a table?
One specific case is where we have a master table, a detail table and a sub-detail table. The details table includes a field that calculates the total amounts of the sub-details in a manner like this:
Then the master table includes a field that calculates the total amount for the master in a manner like this:
Will this cause one set of cascading updates when we change a sub-detail item, causing the related detail row to recalculate its total and its related master row to recalculate its total? Or does every read of the master record cause cascading filters such that the master record filters down to each detail and then each detail filters down to each sub-detail? Or does that calculation work in some other way that is more efficient?
Another question we have is how much overhead it creates to store a commonly-used calculation in a table. An example would be an estimated arrival date for a package that is calculated as the shipping date plus seven days. We would display this on several screens and possibly use it in filters, but wouldn't need to reference it 90% of the times we read the row. Is it more efficient for the table to have a calculated field called estimatedArrivalDate with a formula like =actualShipDate+7 or to include that calculation in each screen and filter where we need it?