HI Alexandre

> It could be avoided if the summary table has more then one record per
> region/SendTable, and instead of an update the trigger insert a new row
> with value +1 for marking it as T and a row with -1 for marking it as False.
>
> And summarize it daily again.
>
> Then to get the current value use a sum(Column) for all the values.
>
> The same approach discussed here a lot of times to improve a select
> count(*) on a huge table.
>
> It would be a lock free solution.
>
> see you !
>
>

I hadn't considered the locking issue. The web service has one App per
region so I had assumed that only one app (user) would be updating a
particular regions count at any one time. However we do have another
app the inserts and deletes records from BranchSend. This could cause a
dead lock. If I move to your suggestion of summerising a table would
the following be the correct way of summarising the table...

in read only transaction
Get current max pk for a region / sendtable
Get sum of all entries less than or equal to pk
Commit write transaction

in a write transaction
Delete all entries for region / sendtable less than or equal to pk
Insert 1 record for region / table with total
Commit write transaction


Thanks for the great suggestion. I think this also means I can easily
implement this structure into a live DB !
Rob