At 01:14 AM 1/3/2003 +0700, sugi wrote:

>To be more specific, I needed to provide a server-side construct to
>calculate some data (summary aggregates) for a table. The operation
>is fairly simple, (can be done as a view or an sp), but I'm concerned
>about performance issues when the db got bigger.
>
>I had several options when I started :
>1. Computed Field.
>2. SP (returns single value, CALC_ONE in my previous post)
>3. SP (returns a set, CALC_TWO in my previous post)
>4. VIEW

Each of those alternative will perform the calculation each
time you reference the computed value. If you need the
computed value often and change the underlying values seldom,
you might consider a field maintained by triggers. In the
simplest case, you add triggers to each of the tables involved
in the computation and update the table containing the
calculated value. Unfortunately, that will cause deadlocks
(update conflicts) if two transactions update the source
values at the same time.

A more complex scheme tracks the source values. In the
case of an average, you might have a table that maintains
the sum and another that maintains the count. When a
record is added a new row is inserted in those table,
+1 for the count and +n for the sum. A delete creates
a row containing -1 for the count and -n for the sum.
Periodically, run a program that consolidates the value
so the count table might look like this:

2456
1
1
-1
1


If you need the computed values seldom and frequently
change the source values, then one of the choices above
will work better.


Regards,

Ann
www.ibphoenix.com
We have answers.