Alexandre Benson Smith wrote:

>IIRC Ann has pointed sometimes when someone needs a count of registers.
>
>The solution is very simple and fast.
>
>Keep a table with a column called for example counter
>Create Table InvoiceCount(Counter integer)
>
>then create an after insert trigger like this:
>create trigger TI_Invoice_Counter for Invoice after insert as
>begin
> Insert into InvoiceCounter (Counter) values(1);
>end
>
>do the same for the delete but add -1 instead.
>
>then to get the total count you use this query:
>
>Select sum(counter) from InvoiceCount
>
>Then you asks me, but how does this differ from a count ? How can I gain
>speed with this ?
>
>Simple, from time to time (monthly, daily, hourly, ???) you sum all the
>counts and remove all the records and put just one record with the
>total, the triggers will continue to populate the table so you will have
>something like this:
>Counter
>123003
>1
>1
>1
>1
>-1
>1
>
>when you asks for a sum for that column you will get 123007 and will run
>trough 7 records to get it instead of 123007, and the insert aproach
>doens't causes lock conflicts and thus doesn't become a bottleneck.
>
>see you !
>
>

YES! Very nice solution to both problems. Very good.