Best Practices for Grand Total field

[Dan Pouliot]

forgive me if this question has been answered elsewhere. I'll try to be brief:

I have a table "events", and another table "line items".

Users are complaining that event invoices are slow. There are many potential areas that I could look at for speeding it up, but right now I'm looking at the grand total.

 

Historically I have used this calculation field in line items table:

 

Total of Total Cost +

If(

Line Items to Events to Payment Types:: d.c. Tax Exempt ≠ "yes"; 

Total of Food Bev Tax Amount+

Total of Misc Tax Amount+

Total of Parking Tax Amount

)

 

however, I recently learned about the sum () aggregate function, so I was wondering if creating this calculation field in events table would make any speed difference:

 

Sum(Events to Line Items::Total Cost) +

If(

Events to Payment Types:: d.c. Tax Exempt ≠ "yes";

Sum(Events to Line Items::Food Bev Tax Amount) +

Sum(Events to Line Items::Goods Tax Amount) +

Sum(Events to Line Items::Parking Tax Amount) 

)

 

 

From a speed perspective, is one preferable? I suspect the latter, as it eliminates the need for the "Total of..." summary fields. Thanks in advance.

[eos]

If you're talking not about real summary fields for an arbitrary found set, but rather about static totals for Events parent records, then from a speed perspective it makes more sense to create and store these results in real time, i.e. each time you add/delete/edit the relevant fields/parameters, update the results field(s) via script.

 

In essence, this means trading one huge performance hit when displaying records against many small performance penalties while working with the parent and/or child records. The advantage is that the display is much faster, since instead of (par force) unstored calc fields that have to be re-calculated continuously, you're displaying stored fields.