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...
>

It doesn't much matter whether the computed field is driven from
a select statement or a stored procedure. In both cases, the statement
will be compiled on first reference and maintained in compiled format
until the table is referenced. The performance differences should
be minimal.

Either solution will work adequately in a system where you spend more
time inserting, updating, and deleting details than reading the master.

If your application tends to insert details once and not change them,
you might consider adding a trigger on the detail to update the master.
That can cause deadlocks if two transactions insert, update, or delete
details of the same master at the same time, but minimizes the cost of
reading the master.

Regards,


Ann