Robert martin wrote:

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

I would do something like:

CREATE TABLE BranchSendCount
(
RegionRef BigInt NOT NULL,
SendTable BigInt NOT NULL,
AvailableRecs Bigint,
)^

Create index SK_BranchSendCount on BranchSendCount (RegionRef, SendTable);

CREATE TRIGGER BranchSend_Insert FOR BranchSend
BEFORE INSERT POSITION 10
AS BEGIN
IF (NEW.TransferConfirmed = 'F') THEN
Insert into BranchSendCount values (NEW.RegionRef, NEW.SendTable, 1);
END^

CREATE TRIGGER BranchSend_Delete FOR BranchSend
AFTER DELETE POSITION 10
AS BEGIN
IF (OLD.TransferConfirmed = 'F') THEN
Insert into BranchSendCount values (NEW.RegionRef, NEW.SendTable, -1);
END^


CREATE TRIGGER BranchSend_Edit FOR BranchSend
AFTER EDIT POSITION 10
AS BEGIN
IF (OLD.TransferConfirmed = 'F')
AND (New.TransferConfirmed = 'T') THEN

Insert into BranchSendCount values (NEW.RegionRef, NEW.SendTable, -1);


IF (OLD.TransferConfirmed = 'T')
AND (New.TransferConfirmed = 'F') THEN

Insert into BranchSendCount values (NEW.RegionRef, NEW.SendTable, 1);


END^



create procedure SummarizeBranchSend as

declare variable RegionRef BigInt;
declare variable SendTable BigInt;
declare variable AvailableRecs Bigint;

begin
for
select
RegionRef, SendTable, sum(AvailableRecords)
from
BranchSendCount
group by
1, 2
into
:wRegionRef, wSendTable, wAvailableRecords
do begin
delete from BranchSendCount where RegionRef = :wRegionRef and SendTable = :wSendTable;
insert into BranchSendCount values (:wRegionRef, :wSendTable, :wAvailableRecords);
end;

end;

for the first time, run this:

Insert into BranchSendCount select RegionRef, SendTable, count(*) from BranchSend where TransferConfirmed = 'F' group by 1, 2

put a cron/at job to run the SummarizeBranchSend procedure at regular intervals (daily ?).

To get the current total available records do

select sum(AvailabeRecords) from BranchSendCount where RegionRef = :RegionRef and SendTable = :SendTable;

Just after the SummarizeBranchSend procedure run, BranchSendCount will have just 1 record for each RegionRef/SendTable combination, with the total of records not transfered, during the day more records would be added for each RegionRef/SendTable combination with a value +1 for a non confirmed record and -1 for a confirmed record, so each RegionRef/SendTable combination would have more than one record, but just a few to summarize when needed, once the SummarizeBranchSend procedure runs regularly the table would not grow a lot.

I wrote it in a hurry, hope the logic is ok... But anyway you will get the idea. I am sure others would point out if there is an error on the logic above.

If you want an extra level of security you could change the summarize procedure to something like:

create procedure SumarizeBranchSend as

declare variable RegionRef BigInt;
declare variable SendTable BigInt;
declare variable AvailableRecs Bigint;

begin
delete from BranchSendCount;

for
select
RegionRef, SendTable, count(*)
from
BranchSend
where
TransferConfirmed = 'F'
group by
1, 2
into
:wRegionRef, wSendTable, wAvailableRecords
do begin
insert into BranchSendCount values (:wRegionRef, :wSendTable, :wAvailableRecords);
end;

end;

so you will do a full count once in a while... This would avoid a first run of the statement to to the first summarization, just run the procedure...


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br