--- In firebird-support@yahoogroups.com, Bogus³aw Brandys
<brandys@o...> wrote:

> Alan McDonald wrote:
> >>Hi Adam,
> >>
> >>In the stored procedure,
> >>
> >>
> >>>BEGIN
> >>> FOR SELECT ITEM_ID, SUM(QUANTITY)
> >>> FROM SUMMARYTABLE
> >>> GROUP BY ITEM_ID
> >>> INTO :ITEM_ID, :QUANTITY
> >>> DO
> >>> BEGIN
> >>> DELETE FROM SUMMARYTABLE
> >>> WHERE ITEM_ID = :ITEM_ID;
> >>>
> >>> insert into summarytable (item_id, quantity)
> >>> values (:ITEM_ID, :QUANTITY);
> >>> END
> >>
> >>
> >>Would there be a problem if after the sp has read the records but
> >>before the deletion, additional records for the item are inserted
> >>into the table? How can I ensure that the deletion only applies to
> >>the records that have been read by the cursor, and not to all
> >>records? Thanks.
> >>
> >>Ray Mond
> >
> >
> > that's why we have transactions. Records being inserted after the

select

> > won't be visible during this transaction.
> > Alan
>
>
> Hi
>
> This would be perfect solution but soon someone may expect a big
> garbagge here if this procedure is running too often.Am I right ?
>
> Regards
> Boguslaw Brandys

There are a couple of "optimisations" you can do to minimise the
garbage. Because I wanted to get to the point, I kept the SP pretty
simple. If you think about it, summing less than say 5 records is
going to be a pretty quick operation. Even if the field is indexed,
the selectivity will be pretty reasonable.

The example SP did a brute force sum / delete / insert, even if there
was only one or two records for a particular item_id. I would suggest
that for this few operations, the costs of the new record version
would not be worth the savings, but obviously if there were 1000
records, then the solution looks quite attractive.

So use a having clause on the stored procedure query to come up with a
compromise. I have no idea what value would be a good point, maybe
having count(*) > 20 woud be a good place to start.

But providing the transactions don't get "stuck", the old versions
will be garbage collected and reused. It really depends on the fill
rate of the table with all the triggers. You may only have to run the
procedure once a month, or it may need to run every 5 minutes to keep
it reasonable.

Adam