| Subject | Re: [IBO] Calculating data |
|---|---|
| Author | Geoff Worboys |
| Post date | 2014-09-05T07:49:40Z |
tblac@... [IBObjects] wrote:
(Past difficulties mean I try to avoid and cross-table
references from within a computed field.)
Rob's dynamic recalculation suggestion probably remains the
best while performance is not an issue (and with only 200
records I doubt that it is). You could wrap his suggestion
into a view, or a selectable stored procedure, to save having
to rewrite it every time.
The advantage of using either of those (but particularly the
stored procedure if the calculation may start to get messy) is
that you can centralise the details of how you're doing it, and
change the implementation later without affecting other code.
Marcin's idea of using triggers to maintain the totals can
work, but you have to watch out for contention between updates.
(eg: Two users updating TableA or TableB at the same time will
try to also update TableC with the totals.)
The real problems start when you begin to talk about thousands
or millions of rows and the performance of summing totals
becomes an issue. That's when you start to need some sort of
regular maintenance procedure to keep the totals. You can
combine Marcin's trigger idea by using a TableC2, where the
triggers insert (not update) total adjustments to be merged
with the next maintenance run. Then use a view over TableC to
combine the total fields in TableC with the the sum of the
adjustments from TableC2. Messy, but it keeps the users out
of the way of each other and lets your transactions do their
thing.
--
Geoff Worboys
Telesis Computing Pty Ltd
> Hi, I have asked this question previously and Robert MartinI'd stay clear of using computed fields for this purpose.
> suggested a possible solution. See
> 47630Re: [IBO] Update summary in TableA based on records in
> TableB
> I wanted to know what are the pros and cons of using other
> options such as Stored Procedures or Calculated fields.
> This must be a common problem. I have several tables. I want
> to sum the field "HOURS" in TableA, add it to the sum of
> "HOURS" in TableB and put it all in TableC
> The tables contain only 200 records each so I could use a
> loop on each table and calculate this but the user would
> have to wait.
(Past difficulties mean I try to avoid and cross-table
references from within a computed field.)
Rob's dynamic recalculation suggestion probably remains the
best while performance is not an issue (and with only 200
records I doubt that it is). You could wrap his suggestion
into a view, or a selectable stored procedure, to save having
to rewrite it every time.
The advantage of using either of those (but particularly the
stored procedure if the calculation may start to get messy) is
that you can centralise the details of how you're doing it, and
change the implementation later without affecting other code.
Marcin's idea of using triggers to maintain the totals can
work, but you have to watch out for contention between updates.
(eg: Two users updating TableA or TableB at the same time will
try to also update TableC with the totals.)
The real problems start when you begin to talk about thousands
or millions of rows and the performance of summing totals
becomes an issue. That's when you start to need some sort of
regular maintenance procedure to keep the totals. You can
combine Marcin's trigger idea by using a TableC2, where the
triggers insert (not update) total adjustments to be merged
with the next maintenance run. Then use a view over TableC to
combine the total fields in TableC with the the sum of the
adjustments from TableC2. Messy, but it keeps the users out
of the way of each other and lets your transactions do their
thing.
--
Geoff Worboys
Telesis Computing Pty Ltd