WOW

Thanks for all your time !!!!!


Re your summarise SP...


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;



I assume this is safe only if run in a snapshot (forget the phrase)
transaction, otherwise entries added while the SP is running might get
deleted but not summed?

Will look at implementation !

Love the solution
Rob


Alexandre Benson Smith wrote:

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