Re: performance question: sum values vs. store calculations

If you have to calculate the sum on the fly, then you need to find the 50 - 100 relevant rows. The nature of banking systems is such that these 50-100 rows will have appeared spread over a long period of time. Consequently there is a very high probability that all of the 50-100 row requests will result in a real physical read -

Even on very fast disks 50 - 100 random reads in less than one second is not possible.

However, you might consider IOTs, (index organised tables), which could be used to ensure that all transactions relating to a single account are clustered into a very small number of blocks. (The same effect could be achieved using an INDEX CLUSTER in Oracle 7). In this case, a request for the 50 - 100 rows could probably be satisfied in 3 or 4 physical reads, giving you a chance of meeting the sub-second requirement. There are, however, other side-effects to consider.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

erik_ostermueller_at_my-deja.com wrote in message <7qfkq5$si9$1_at_nnrp1.deja.com>...
>The legacy banking system that we are rewriting stores the results of many
>calculations in tables. For instance, when a balance is calculated for an
>account for a new transaction, the new balance is stored on the record for
>the account. For flexibility and maintainability reasons, we would prefer
>to store only the atomic transaction data, and derive/calculate the balance
>(and other calculations) when requested.We know that the tradeoff for this
>decision is a performance hit, but don't know how big it will be.Each
>calculation will involve about 50-100 records from a total of 1.5 billion
>records. This will be a 24x7 system that expects sub-second response time.
>Does anyone out there have some experience with this?
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Mon Sep 06 1999 - 04:38:11 CDT