--- In firebird-support@yahoogroups.com, "Eric" <Eric@...> wrote:

>
> Hello,
>
> I'm wondering what is better in performance - against database design..
>
> I have an order table (master) in which I calculate the totals of the
> lines table (detail)
>
> What is better?
>
> 1. add a computed field to the order table and calculate it by a (Select
> Qty * price where MasterID = DetailID)
>
> or
>
> 2. compute the field through a stored procedure in which the totals
> where calculated.
>
> The last option has the advantage the select queries are faster, but the
> "Total-amount" field is redundant in the database and the chance of
> errors is bigger...
>
> Thanks,
>
>
> Eric

Eric,

In the past I have seen performance issues with cross-table calculated
fields. They were used in our application before I was this familiar
with Firebird, so I can not tell you whether it was just the way I
defined them. If you do go with calculated fields, make sure you test
the performance with large-ish tables.

Since then, we have stuck with the second approach for similar
problems. The chance of errors is not an issue if you use triggers to
maintain the value (any more than you chance an error by incorrectly
defining a calculated field). If you do have the possibility of two
different transactions modifying two detail records of the same master
record 'simultaneously', then you will obviously get lock conflicts.

Alternatively to 1, you could also define a view instead of the
calculated field. Whilst that will not improve the performance of the
select, it does mean that if you use some db admin tool, you are not
waiting forever when browsing that table.

Of course, the approach also depends on the nature of the data. If it
is largely static once in there and heavily reported on, then I would
think the second approach is more efficient. If it is largely
changeable and seldom reported on, the first approach may be better.

Adam