File store (was Re: Salutations)

David Merrill dmerrill@lupercalia.net
Mon, 11 Dec 2000 16:12:16 -0500


On Tue, Dec 12, 2000 at 06:43:17AM +1000, Phillip J Shelton wrote:
> Sorry to be dense.  Isn't there some way to have the DB store the current
> balance with each record? That way the  current balence would just be the
> current balence from the last record.
> 
> Or is that more expensive than I realise?

It's possible, yes. Off the top of my head I can't think of any way of
implementing a running total that isn't expensive. This technique is
expensive to the db server, while others are expensive at the client.

Editing a record would require an update statement along the lines of:

update ledger replace running_total with running_total + (delta) where
ledger_id > this_ledger_id. This assumes ledger_id represents the
order in which the entries appear in the ledger, therefore based on
the event date and order of entry into the ledger.

The expense is mitigated by the fact that:

1) it can be performed as a single SQL statement as above, so you
never have to walk a table. Db engines are optimized for this sort of
update, so it *should* execute in sub-second time even on large data
sets.

2) an entry so modified is highly likely to be near the end of the
dataset, because it is highly likely to be a recent entry

3) it can be implemented in a trigger, where supported by the database

4) edits don't happen *that* often as a percentage of overall changes.
Perhaps (judging from how often *I* do one, which is obviously
statistically meaningless) somewhere in single digits (2%? 5%?).

But it is aggravated by the fact that you have to acquire a lock on
*all* the records that follow this one before you can perform the
update. One way of getting around this might be to have a related
table that contains the running total.

LEDGER        RUNNING_TOTAL
------        -------
ledger_id<--->ledger_id
amount        running_totel

The RUNNING_TOTAL table is mapped 1:1 with the LEDGER table. Updating
the running total requires an update against running_total ONLY, so
there is never a lock on the regular ledger table. It is a bit kludgy,
but optimizing database use is often that way. Mind you, I'm not
proposing this as the solution, only a possible solution if we can't
think of a better one.


-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

You have acquired a scroll entitled 'irk gleknow mizk'(n).--More--

This is an IBM Manual scroll.--More--

You are permanently confused.
		-- Dave Decot